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.

Our table work might have three columns: id, firstName, lastName.

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 TABLE_CONSTRAINTS query.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'work'

We might see a result with a CONSTRAINT_NAME and CONSTRAINT_TYPE.

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 COLUMN_NAME

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.