How to alter length of varchar in composite primary key?

Question

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

Answer 1

By altering the datatype to varchar(4000), you make it 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 that the index size (which is implicitly create for PK) is limited to 900 bytes and inserts of greater values will fail.

Answer 2

Do not be surprised if you get a warning when you create this index in the end, you are giving it the potential to create an index key greater than the allowed 900 bytes. (Since the PK will either be the clustered index (default) or an NC index enforcing it.)