SQL DROP CONSTRAINT Statement

The DROP CONSTRAINT statement in SQL is used to remove a constraint from a table. Constraints are rules that enforce data integrity, ensuring that data inserted or updated into a table meets specific requirements. Removing a constraint makes the column less restrictive, but this can impact the integrity of the data stored in the table.

DROP CONSTRAINT: Definition and Usage

DROP CONSTRAINT is a powerful command for modifying your database schema. It's crucial to understand the implications of dropping a constraint as it could lead to inconsistencies in your data. Always back up your database before dropping constraints.

Syntax

The basic syntax is database-system specific, but the general pattern is shown below. The specific naming and approach vary slightly across MySQL and other systems.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
      

--For primary key constraints:
ALTER TABLE table_name DROP PRIMARY KEY;

--For other constraints:
ALTER TABLE table_name DROP INDEX constraint_name;  
      

Replace table_name with the name of your table and constraint_name with the name of the constraint.

Examples

Dropping a UNIQUE Constraint

This shows how to drop a unique constraint named 'UC_Person' in SQL Server, Oracle, and MS Access.

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
      

ALTER TABLE Persons
DROP INDEX UC_Person;
      

(The unique constraint UC_Person is dropped from the Persons table.)
      

Dropping a PRIMARY KEY Constraint

This removes a primary key constraint.

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
      

ALTER TABLE Persons
DROP PRIMARY KEY;
      

(The primary key constraint PK_Person is dropped from the Persons table.)
      

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
      

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
      

(The foreign key constraint FK_PersonOrder is dropped from the Orders table.)
      

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
      

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
      

(The CHECK constraint CHK_PersonAge is dropped from the Persons table.)