SQL Query to Remove Primary Key

The primary key is the key that may contain one or more columns that uniquely identify each row in a table. We can add a primary key constraint while creating the and we can add or remove after by using ALTER command on our table of the database.

In this article let us see how we can remove a  primary key constraint in a table using MSSQL as the server.

Step 1: Creating a Database

We use the below command to create a database named GeeksforGeeks:

Query:

CREATE DATABASE GeeksforGeeks

Step 2: Using the Database

To use the GeeksforGeeks database use the below command:

Query:

USE GeeksforGeeks

Output:

Step 3: Creating the table

Create a table student_details with  3 columns using the following SQL query:  

Query:

CREATE TABLE student_details(
  stu_id VARCHAR(8) NOT NULL PRIMARY KEY,
  stu_name VARCHAR(20),
  stu_branch VARCHAR(20)
  );

Output:

Step 4: Verifying the database 

To view the description of the table using the following SQL query as follows.

Query:

EXEC sp_columns student_details

Output:

Step 5: Inserting data into the Table  

Inserting rows into student_details using the following SQL query:

Query:

INSERT INTO student_details VALUES
('1940001','PRATHAM','E.C.E'),
('1940002','ASHOK','C.S.E'),
('1940003','PAVAN KUMAR','C.S.E'),
('1940004','SANTHOSH','E.C.E'),
('1940005','THAMAN','E.C.E'),
('1940006','HARSH','E.E.E')

Output:

Step 6: Verifying the inserted data

Viewing the tables student_details,student_branch_details,student_address after inserting rows by using the following SQL query:

Query:

SELECT* FROM student_details 

Output:

Step 7: Knowing the constraints of the table using the following SQL query.

Query:

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE  

Output:

Here in output ‘PK’ means primary key. The constraint can be known from the object explorer as well.

Step 8: Here we use the Drop constraint to remove the primary key from the database. DROP is used to delete a whole database or just a table. The DROP statement destroys the objects like an existing database, table, index, or view. A DROP statement in SQL removes a component from a relational database management system (RDBMS).

Query:

Removing the primary key constraint using the following query:

Query:

ALTER TABLE student_details
DROP CONSTRAINT PK__student___E53CAB21F07312DD

The primary key constraint is now removed:

Output: