In MSSQL I have a table created like 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 I want to increase the length of the 'data' column from 255 to 4000.

If I just try:

ALTER TABLE [mytable] ALTER COLUMN data varchar(4000);

Then I get this error:

The object 'PK_mytable_data' is dependent on the column 'data'

If I 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 I get this error:

Cannot define PRIMARY KEY constraint on nullable column in table 'mytable'

What am I missing? Both columns were defined with NOT NULL, so why is MSSQL reporting that it can't recreate this constraint after I drop it?

Thanks! Evan

Edit
Report