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 geeks for geeks table in a database called “geeks“.

Step 1: Creating the database 

The database is created in SQL Server using the following command 

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

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

Query:

USE geeks;

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 geeks for geeks table in the geeks database

Query:

CREATE TABLE geeksforgeeks(
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 geeksforgeeks 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 geeksforgeeks;

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 geeksforgeeks DROP CONSTRAINT PK__geeksfor__3214EC275032BA6D;

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

Query:

ALTER TABLE geeksforgeeks 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.

romy421kumari

Follow

Improve

Article Tags :

• Blogathon
• SQL
• Blogathon-2021
• SQL-Server