Can I change a varchar constraint in SQL Server 2005 after I have defined my PK's and FK's?

Can I change a va开发者_运维问答rchar constraint in SQL Server 2005, after I have defined my PK's and FK's?

varchar(10) ----> varchar (50) 

If the column in question is part of a foreign key constraint, then obviously not - the data types on both sides of the constraint must match exactly, and an ALTER TABLE statement can only affect a single table at a time.

If this is just another column in a table that has a foreign key constraint, then yes, it can be altered.

If the column is just part of a primary key or unique constraint, and is not referenced by a foreign key, it can be altered. It took me ~30 seconds to write this:

create table T1 (
    ID varchar(10) not null PRIMARY KEY,
    Val1 varchar(10) not null UNIQUE
)
go
insert into T1 (ID,Val1) values ('abc','def')
go
alter table T1 alter column Val1 varchar(50) not null
go
alter table T1 alter column ID varchar(50) not null

It runs without errors.

Where the column is involved in a foreign key, you will need to drop the FK constraint, change the data type, then recreated the foreign key. Very trivial, actually, especially if, before dropping constraints, you script them out using SSMS because changing the data type does not affect the FK constraint's definition; the issue is just that you can't change the data type while the constraint is in place.