CODEWITHSUNDEEP
sqlsql-serverprimary-keycomposite-primary-key
Jason Rae
Jason Rae

Reputation: 2663

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

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?

Upvotes: 133

Views: 282012

Answers (6)

Saad Mahmood
Saad Mahmood

Reputation: 185

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

Upvotes: 5

MNF
MNF

Reputation: 717

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
)

Upvotes: 4

Oky
Oky

Reputation: 63

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.

Upvotes: 4

Garrett Taiji
Garrett Taiji

Reputation: 45

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

for example:

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

Upvotes: -4

darnir
darnir

Reputation: 5180

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

Upvotes: 204

Oleg Dok
Oleg Dok

Reputation: 21756

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.

Upvotes: 26

Related Questions