SQL Query to Drop Unique Key Constraints Using ALTER Command

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