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
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
The database is created in SQL Server using the following command
CREATE DATABASE geeks;
Step 2: Using the Database
Use the below SQL statement to switch the database context to geeks:
In SQL Server, the Primary key constraint is defined by the 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:
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
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
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
SELECT * FROM geeksforgeeks;
Check which column is set as Primary key, we can use following command:
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ;
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:
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)
ALTER TABLE geeksforgeeks ADD PRIMARY KEY (EmpID);
Now check the primary key of the table using ‘SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ‘ command
In the above image, you can see EMPID is the primary key.