How to drop a constraint in SQL?
Syntax
Most of the time when you want to drop a column with a constraint, you need to drop the constraint first. That can be a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint. To drop the constraint, we use DROP CONSTRAINT command.
Here is the generic syntax for dropping a constraint:
ALTER TABLE table_name
DROP CONSTRAINT const_name;
That applies to PostgreSQL, MS SQL Server, Oracle. For MySQL, the syntax might be a little more specific for each type of constraint:
- To drop a UNIQUE Constraint:
ALTER TABLE table_name
DROP INDEX const_name;
- To drop a PRIMARY KEY Constraint
ALTER TABLE table_name
DROP PRIMARY KEY;
- To drop a FOREIGN KEY Constraint
ALTER TABLE table_name
DROP FOREIGN KEY const_name;
- To drop a CHECK Constraint
ALTER TABLE table_name
DROP CHECK const_name;
Example
To drop a constraint fk_emp_id from the table employees in SQL Server:
ALTER TABLE employees
DROP CONSTRAINT fk_emp_id;
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
