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.