How to Change the Primary Key to Another Column in SQL Server
How can we change the primary key to another column in a SQL Server?
Suppose we have a database
db with a table of name
work might have three columns:
We want to set
id to be our primary key, but there might be an existing primary key constraint.
Get current primary key constraint in SQL
If we don’t know the name of the primary key constraint, we can obtain it with a
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'work'
We might see a result with a
CONSTRAINT_NAME CONSTRAINT_TYPE IS_DEFERRABLE INITIALLY_DEFERRED PK_work PRIMARY KEY NO NO
In this case,
PK_work is our constraint that we want to modify. We can see exactly which constraint each of the columns in the table is associated with using the following query:
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'work'
We can now associate each
CONSTRAINT_NAME with a
CONSTRAINT_NAME COLUMN_NAME ORDINAL_POSITION PK_work firstName 1 PK_work lastName 2
Update the primary key in SQL
Now that we know the primary key constraint name, we can update the constraint with another column.
ALTER TABLE work DROP CONSTRAINT pk_work ALTER TABLE work ADD CONSTRAINT pk_work PRIMARY KEY (id)
First, we are dropping the existing constraint, then we’re adding the new one back in with the updated column.