sql server - How to alter length of varchar in composite primary key? -

in mssql have table created this:

create table [mytable] (fkid int not null, data varchar(255) constraint df_mytable_data default '' not null); alter table [mytable] add constraint pk_mytable_data primary key (fkid, data); 

now want increase length of 'data' column 255 4000.

if try:

alter table [mytable] alter column data varchar(4000); 

then error:

the object 'pk_mytable_data' dependent on column 'data' 

if try this:

alter table [mytable] drop constraint pk_mytable_data; alter table [mytable] alter column data varchar(4000); alter table [mytable] add constraint pk_mytable_data primary key (fkid, data); 

then error:

cannot define primary key constraint on nullable column in table 'mytable' 

what missing? both columns defined not null, why mssql reporting can't recreate constraint after drop it?

thanks! evan

by altering datatype varchar(4000), make accept nulls.

try this:

alter table [mytable] drop constraint pk_mytable_data; alter table [mytable] alter column data varchar(4000) not null; alter table [mytable] add constraint pk_mytable_data primary key (fkid, data); 

note index size (which implicitly create pk) limited 900 bytes , inserts of greater values fail.