How to change primary key constraint using SQL syntax?

Question

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

What syntax can I use for this? Should I drop and recreate the key constraint?

Answer 1

Yes. The only way is to reset the constraint using the Alter table, and 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 changed, you can delete it and create it again. For large data sets, this can lead to long runtimes and, therefore, inaccessibility of the table.

Answer 3

Performance is reasonable, it makes no sense to hold non-clustered indexes during this, as they will be updated when deleted and created. If this is a large data set, you should rename the table (if possible, any security settings on it?), Re-creating an empty table with the correct keys, transferring all the data there. You must make sure that you have enough space for this.

Answer 4

@ Darnir's answer can be simplified using:

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

Answer 5

In my case, I want to add a column to the 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 6

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

eg:

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