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?

AFAIK yes, you need to drop and recreate the PK constraint. I don't recall any command to add a column to an already-existing PK/FK constraint. — Seramme, Jan 06 '12 at 17:12

score 195 · Accepted Answer · edited Jun 13 '13 at 11:58

195

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>)

edited Jun 13 '13 at 11:58

Oleg Dok

21,109
4
45
54

answered Jan 06 '12 at 17:20

darnir

4,870
4
32
47

12

If you don't know the primary key constraint name, use query found here to look it up (or look up and drop all at once). [http://stackoverflow.com/a/13948609/945875](http://stackoverflow.com/a/13948609/945875) – Justin Dec 19 '13 at 20:06
if it wasn't clear, or anyone else makes the same mistake, the constraint name can't go in quotes `alter table PatientsInfo drop constraint PK__Patients__1CBB51380A338187` is working here – Maslow Dec 09 '15 at 19:03
3

For those of you, not knowing the constraint name: ALTER TABLE `db`.`table` DROP PRIMARY KEY, ADD PRIMARY KEY (`id1`, `id2`); – karsten314159 Dec 13 '18 at 12:24
3

ALTER TABLE db.table DROP PRIMARY KEY is for MySQL. (Not MSSQL.) – Roy Latham Sep 25 '20 at 16:43

score 25 · Answer 2 · answered Jan 06 '12 at 17:53

25

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.

answered Jan 06 '12 at 17:53

Oleg Dok

21,109
4
45
54

score 4 · Answer 3 · answered Jul 03 '17 at 11:48

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.

score 4 · Answer 4 · answered Jun 11 '19 at 09:34

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
)

score 4 · Answer 5 · answered Jan 13 '22 at 16:15

4

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);

answered Jan 13 '22 at 16:15

Saad Mahmood

167
11

score -4 · Answer 6 · edited May 23 '17 at 12:26

-4

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

for example:

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

edited May 23 '17 at 12:26

Community

1
1

answered Apr 21 '15 at 18:50

Garrett Taiji

45
3

4

The question was how to change the constraint, i.e. ADD a column, not how to rename. – Andrew Steitz Jul 02 '15 at 16:09

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

6 Answers6

Linked