Failing to programmatically DROP primary keys > ALTER the type length of a specific primary key column > ADD them back as primary keys

Question

I have to increase the length of a primary key which appears in several tables. I have to do this programmatically and do it in several databases. I am almost there, and things work individually, but when I put everything together I get an error on the last line ALTER TABLE table1 ADD PRIMARY KEY(PRIMARYKEYCOLUMN)

Msg 1911, Level 16, State 1, Line 30
Column name 'PRIMARYKEYCOLUMN' does not exist in the target table or view.

Msg 1750, Level 16, State 0, Line 30
Could not create constraint or index. See previous errors.

This is the whole query

DECLARE @Col1Len varchar(100) = (SELECT character_maximum_length    
                                 FROM information_schema.columns  
                                 WHERE table_name = 'table1' 
                                   AND column_name = 'Col1')

IF (@Col1Len < 60)
BEGIN 
    DECLARE @PKs TABLE (PRIMARYKEYCOLUMN varchar(100))

    INSERT INTO @PKs (PRIMARYKEYCOLUMN) (
        SELECT column_name AS PRIMARYKEYCOLUMN
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
              ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
              AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
              AND KU.table_name = 'table1')

    SELECT * FROM @PKs

    DECLARE @PK varchar(100) = (SELECT name 
                                FROM sysobjects 
                                WHERE xtype = 'PK'
                                  AND parent_obj IN (SELECT id 
                                                     FROM sysobjects 
                                                     WHERE name = 'table1')
                               )

    DECLARE @Command varchar(100) = 'ALTER TABLE table1 DROP CONSTRAINT ' + @PK
    EXECUTE (@Command)  

    ALTER TABLE table1 
        ALTER COLUMN Col1 varchar(40) NOT NULL

    ALTER TABLE table1 
        ADD PRIMARY KEY(PRIMARYKEYCOLUMN)
END

If I run just the block below, it works and it finds the primary keys and inserts them into @PKs Table.

    DECLARE @PKs TABLE (
        PRIMARYKEYCOLUMN varchar(100)
    )
    Insert into @PKs (PRIMARYKEYCOLUMN) (
        SELECT column_name as PRIMARYKEYCOLUMN
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
            ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
            AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
            AND KU.table_name='table1'
    )
        SELECT * from @PKs

What am I doing wrong?

why `ALTER TABLE table1 ADD PRIMARY KEY(PRIMARYKEYCOLUMN)` if you already know that the column name is `col1`? — Zohar Peled, May 15 '23 at 08:15
There may be more than 1 PK in the table, so I need to add them all back. — ehtio, May 15 '23 at 08:17
That's a composite primary key, not multiple primary keys - there can only be one — Zohar Peled, May 15 '23 at 08:20
Oh I see. I also realise something dumb...if I increase the length I don't have to drop the constrain first! How can I have missed that? So problem solved I guess. — ehtio, May 15 '23 at 08:23
In that case, you might want to delete the question. (even though I can answer it if you'ld like me to) — Zohar Peled, May 15 '23 at 08:27
Well, it would be really good to know how to do it in case the length needs to be decreased or the type changed, please! — ehtio, May 15 '23 at 08:29

score 1 · Accepted Answer · answered May 15 '23 at 08:41

You're getting the error because your alter table statement isn't related to the @PKs table variable. Also, in case of a composite primary key (meaning, a primary key that is the combination of multiple columns), you need to add it as a comma-separated list of column names -
i.e. alter table add constraint pk_table primary key (col1, col2) To do that, you'll need to build the alter table dynamically from the values in the @PKs table variable:

DECLARE @AddPK nvarchar(1000);
SELECT @AddPK = 'ALTER TABLE table1 ADD CONSTRAINT PK_table1 PRIMARY KEY(' + STRING_AGG(PRIMARYKEYCOLUMN, ',') +');'
FROM @PKs

ALTER TABLE table1 DROP CONSTRAINT PK_table1;
ALTER TABLE table1 ALTER COLUMN Col1 nvarchar(10) NOT NULL;
EXECUTE(@AddPK);

Note: String_Agg requires SQL Server 2017 (14.x) or later, if you're using an older version you'll have to use the old workaround of for xml + stuff to concatenate rows into a comma delimited string.

And a side note - instead of varchar(100) for PRIMARYKEYCOLUMN use sysname.

sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it isn't nullable. sysname is used to reference database object names.

I ended up with this, which works perfectly. ```DECLARE @AddPK nvarchar(1000); SELECT @AddPK = 'ALTER TABLE table1 ADD PRIMARY KEY(' + STRING_AGG(PRIMARYKEYCOLUMN, ',') +');' FROM @PKs DECLARE @Command varchar(100) = 'ALTER TABLE table1 DROP Constraint ' + @PK_Table1 EXECUTE (@Command) ALTER TABLE table1 ALTER COLUMN Col1 nvarchar(30) NOT NULL; EXECUTE(@AddPK);``` — ehtio, May 15 '23 at 09:36

Failing to programmatically DROP primary keys > ALTER the type length of a specific primary key column > ADD them back as primary keys

1 Answers1