Alter Column datatype with primary key
primary-key, sql, sql-server-2008
Solution
You need to specify `NOT NULL` explicitly in an `ALTER TABLE ... ALTER COLUMN` otherwise it defaults to allowing `NULL`. This is not permitted in a PK column.
The following works fine.
CREATE TABLE p
(
ReferenceID VARCHAR(6) NOT NULL PRIMARY KEY
)
INSERT INTO p VALUES ('AAAAAA')
ALTER TABLE p ALTER COLUMN ReferenceID VARCHAR(8) NOT NULL
when the `NOT NULL` is omitted it gives the following error
Msg 5074, Level 16, State 1, Line 1
The object 'PK__p__E1A99A792180FB33' is dependent on column 'ReferenceID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ReferenceID failed because one or more objects access this column.
A couple of things to consider in your programmatic approach is that you would need to drop any foreign keys referencing the `ReferenceID` columns temporarily and also make sure you don't include the `NOT NULL` for (Non PK) `ReferenceID` columns that currently are nullable.
Problem
I have a ReferenceID varchar(6) column in over 80 different tables. I need to extend this to a varchar(8) throughout the db following a change implemented by the government organisation that assigns the IDs. I was hoping to declare a cursor to get the table names as follows: ``` DECLARE @TableName AS VARCHAR(200) DECLARE TableCursor CURSOR LOCAL READ_ONLY FOR SELECT t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name = 'ReferenceID' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName ``` and then edit the type as follows: ``` ALTER TABLE @TableName ALTER COLUMN ReferenceID VARCHAR(8) ``` This fails because the column is part of the Primary Key in some of the tables (and the columns included in the PK vary from table to table). I really don't want to have to drop and recreate each PK manually for each table. Within the cursor, is there a way either to disable the PK before altering the datatype and then re-enable it, or to drop and recreate the PK either side of altering the datatype, bearing in mind that the PK will depend on which table we're currently looking at?