Open In App

SQL Query to Drop Unique Key Constraints Using ALTER Command

Improve
Improve
Like Article
Like
Save
Share
Report

Here, we see how to drop unique constraints using alter command. ALTER is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.

Syntax :

ALTER TABLE table_name

DROP CONSTRAINT unique_constraint;

For instance, consider the below table ‘Employee’.

Create a Table:

CREATE TABLE Employee
('ID INT, F_Name CHAR(10), L_Name CHAR(10), Age INT);

Insert values to the table:

INSERT INTO Employee
VALUES('1','Rahul','Pal','20');
INSERT INTO Employee
VALUES('2','Ajay','Soni','32');
INSERT INTO Employee
VALUES('3','Jay','Harjai','24');
INSERT INTO Employee
VALUES('4','Ram','Meena','30');

Our table at this point will look like below:

Employee

ADD Unique Constraint Using ALTER:

To add a unique constraint to the table use the below statement:

ALTER TABLE Employee
ADD CONSTRAINT/INDEX unique_id UNIQUE (ID);

Now, if we add duplicates to it. It will throw the error as below. In order to add duplicates, we need to Drop Unique constraints.

DROP Unique Constraints Query:

Now the below query can be used to drop the unique constraint that we created above:

ALTER TABLE Employee
DROP CONSTRAINT unique_id;

Now let’s try an add duplicates in the table:

INSERT INTO Employee
VALUES('4', 'ABC', 'XYZ', '35');

Since we didn’t get an error, we have successfully removed the unique constraint. Let’s check out the tables to verify the same using the below statement:

SELECT * FROM Employee;

Output:

                                                                                                Employee


Last Updated : 19 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads