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

Solution 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.

Solution 2

you don't have to drop the constraint, simply NOCHECK it

IF EXISTS 
(SELECT 1 FROM sys.tables tab INNER JOIN sys.columns col ON tab.object_id = col.object_id      WHERE tab.name = 'MY_TABLE' AND col.name = 'MY_COLUMN')

BEGIN

ALTER TABLE MY_TABLE NOCHECK CONSTRAINT ALL
ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] VARCHAR(50) NOT NULL;
ALTER TABLE MY_TABLE CHECK CONSTRAINT ALL

END

GO

**note that is only going to work in the 'increase' sense, it does not work for decreasing the size because it could cause primary key constraint violations (think if you had two cells of data AAB and AAC, and you decreased the size by one.) For that case you would have to drop the constraint, but not before you have some sql which will store the data in a temp table check to make sure it's going to fit in your new altered column with no dups, and then update back to the new altered table column.

Solution 3

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.)

Comments

[evan.leonard]

Doh. Of course. Thanks. So really the largest I can set the length to is 900 then, yet?

[Quassnoi]

@evan.leonard: actually, 896, since fkid will require 4 bytes to store too.