How can I alter a primary key constraint using SQL syntax?
SqlSql ServerPrimary KeyComposite Primary-KeySql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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>)
Solution 2 - Sql
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.
Solution 3 - Sql
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
)
Solution 4 - Sql
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.
Solution 5 - Sql
PRIMARY KEY CONSTRAINT can only be drop and then create again. For example in MySQL:
ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name ADD PRIMARY KEY (Column1,Column2);
Solution 6 - Sql
you can rename constraint objects using sp_rename (as described in this answer)
for example:
EXEC sp_rename N'schema.MyIOldConstraint', N'MyNewConstraint'