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

User · Accepted Answer

Yes  The only way would be to drop the constraint with an Alter table then recreate it   ALTER TABLE  lt Table Name gt  DROP CONSTRAINT  lt constraint name gt   ALTER TABLE  lt Table Name gt  ADD CONSTRAINT  lt constraint name gt  PRIMARY KEY   lt Column1 gt   lt Column2 gt

User · Answer

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

User · Answer

you can rename constraint objects using sp rename  as described in this answer   for example   EXEC sp rename N schema MyIOldConstraint   N MyNewConstraint

User · Answer

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

User · Answer

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

[sql-server] How can I alter a primary key constraint using SQL syntax?

Examples related to sql-server