Alter primary key in SQL Server -

We may sometimes need to change the primary column in SQL. We can’t exactly do so with one simple alter statement instead it can be done by dropping the existing primary key constraint and recreate a new one.
Let us take example how we could alter primary key in SQL Server in action:
For example we have this definition of a table where we have the primary key on ID column but we would like to use ContactID column instead.

CREATE TABLE dbo.tContacts(
	ID int NOT NULL 
		CONSTRAINT PK_tContacts_ID Primary Key CLUSTERED,
	ContactID int NOT NULL,
	FirstName varchar(50) NULL,
	LastName varchar(50) NULL,
	Company varchar(50) NULL,
	[Address] varchar(50) NULL,
	City varchar(50) NULL,
	County varchar(50) NULL,
	[State] varchar(50) NULL,
	ZIP varchar(50) NULL,
	Phone varchar(50) NULL,
	Fax varchar(50) NULL,
	Email varchar(50) NULL,
	Web varchar(50) NULL	
);
GO

If we simply issue an alter to the primary key we will get an error that look like the one below

--Change the primary key to ContactID
ALTER TABLE dbo.tContacts
ADD CONSTRAINT PK_tContacts_ContactID PRIMARY KEY CLUSTERED (ContactID);
GO

To modify a PRIMARY KEY constraint let us first delete the existing PRIMARY KEY constraint and then re-create it with the new definition. Notice the new primary key candidate column must satisfy the requirement to become a primary key. For example the new column should have non empty constraint and shouldn’t have duplicate values.

--Drop the existing primary key alter primary key to ContactID
ALTER TABLE dbo.tContacts
DROP CONSTRAINT PK_tContacts_ID;
GO
 
ALTER TABLE dbo.tContacts
ADD CONSTRAINT PK_tContacts_ContactID PRIMARY KEY CLUSTERED (ContactID);
GO

Let us check if the primary key is constraint is moved from ID column to ContactID column using SP_Help procedure.

--Check if we actualy changed the primary key column
Exec SP_HELP tContacts;

As we could see on the bottom row the primary key constraint moved to ContactID indeed.