How to alter length of varchar in composite primary key?
constraints, primary-key, sql-server, sql-server-2005, sql-server-2008
Solution
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.
Problem
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