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?
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>)
alter table PatientsInfo drop constraint PK__Patients__1CBB51380A338187 is working here
db.table DROP PRIMARY KEY, ADD PRIMARY KEY (id1, id2);
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.
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);
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.
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
)
you can rename constraint objects using sp_rename (as described in this answer)
for example:
EXEC sp_rename N'schema.MyIOldConstraint', N'MyNewConstraint'
