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:
Contact Us