Change Primary Key Column in SQL Server

Primary Key refers to the column of a table that uniquely identifies a row in a table. It contains unique values and can not contain NULL values. For the purpose of the demonstration, we will be creating Beginner for Beginner table in a database called “Beginner“.

Step 1: Creating the database 

The database is created in SQL Server using the following command 

Query:

CREATE DATABASE Beginner;

Step 2: Using the Database

Use the below SQL statement to switch the database context to Beginner:

Query:

USE Beginner;

In SQL Server, the Primary key constraint is defined by the syntax:

Syntax:

CREATE TABLE tablename (
column_name datatype NOT
NULL PRIMARY KEY);

We can also define the primary key after making the table, but make sure the column has the NOT NULL constraint on it. The syntax for declaring primary key after defining the table:

Syntax:

Alter table table_name add  primary key (column_name);

To change the Primary key column in the SQL Server, follow these steps:

• Drop already defined primary key.
• Add a new column as the primary key.

Step 1: Table creation

We have the following Beginner for Beginner table in the Beginner database

Query:

CREATE TABLE w3wiki(
ID int PRIMARY KEY,
FIRSTNAME varchar(30),
LASTNAME varchar(30),
CITY varchar(18),
EmpID int NOT NULL
);

Step 2: Insert data into the table

Query:

INSERT INTO w3wiki VALUES 
(1,'Romy', 'Kumari', 'Delhi',1900089),
(2,'Avinav', 'Pandey', 'Delhi',1909089),
(3,'Nikhil', 'Kalra', 'Punjab',1000089),
(4,'Mansi', 'Lal', 'Uttarpradesh',1905689),
(5,'Rinkle', 'Arora', 'Haryana',1900989),
(6,'Sakshi', 'Kumari', 'Delhi',1700089),
(7,'Soumya', 'Shriya', 'Bihar',1660089),
(8,'Mitu', 'Kumari', 'Rajasthan',1340089);

Step 3: Check the content of table

Query:

SELECT * FROM w3wiki;

Output:

Check which column is set as Primary key, we can use following command:

Query:

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE  ;

Output:

In the above figure, you can see ID column is the primary key. We can drop this primary key by using the CONSTRAINT_NAME mentioned in the image. From here copy CONSTRAINT_NAME to drop the primary key.

Command to drop primary key:

Query:

ALTER TABLE w3wiki DROP CONSTRAINT PK__Beginnerfor__3214EC275032BA6D;

Add EmpID as new Primary key (always check NOT NULL constraint is specified, otherwise, an error will be thrown)

Query:

ALTER TABLE w3wiki ADD PRIMARY KEY (EmpID);

Now check the primary key of the table using ‘SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ‘  command

Output:

In the above image, you can see EMPID is the primary key.