How can I alter a primary key constraint using SQL syntax?

Question

I have a table that is missing a column in its primary key constraint. Instead of editing it through SQL Server, I want to put this in a script to add it as part of our update scripts.

What syntax can I use to do this? Must I drop and recreate the key constraint?

Answer 1

Yes. The only way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

Answer 2

PRIMARY KEY CONSTRAINT cannot be altered, you may only drop it and create again. For big datasets it can cause a long run time and thus - table inavailability.

Answer 3

In my case, I want to add a column to a Primary key (column4). I used this script to add column4

ALTER TABLE TableA
DROP CONSTRAINT [PK_TableA]

ALTER TABLE TableA
ADD CONSTRAINT [PK_TableA] PRIMARY KEY (
    [column1] ASC,
    [column2] ASC, 
    [column3] ASC,
    [column4] ASC
)

Answer 4

Performance wise there is no point to keep non clustered indexes during this as they will get re-updated on drop and create. If it is a big data set you should consider renaming the table (if possible , any security settings on it?), re-creating an empty table with the correct keys migrate all data there. You have to make sure you have enough space for this.

Answer 5

you can rename constraint objects using sp_rename (as described in this answer)

for example:

EXEC sp_rename N'schema.MyIOldConstraint', N'MyNewConstraint'