SQL > ALTER TABLE > Drop Constraint Syntax

Constraints can be placed on a table to limit the type of data that can go into a table. Since we can specify constraints on a table, there needs to be a way to remove this constraint as well. In SQL, this is done via the ALTER TABLE statement.

ALTER TABLE DROP CONSTRAINT removes a named constraint from a table. MySQL uses DROP INDEX for index-based constraints like UNIQUE, while Oracle and SQL Server use DROP CONSTRAINT.

The SQL syntax to remove a constraint from a table is, 

ALTER TABLE "table_name"
DROP [CONSTRAINT|INDEX] "CONSTRAINT_NAME";

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

Assume we want to drop the UNIQUE constraint on the "Address" column, and the name of the constraint is "Con_First." To do this, we type in the following:

MySQL: 

ALTER TABLE Customer DROP INDEX Con_First;

Note that MySQL uses DROP INDEX for index-type constraints such as UNIQUE.

Oracle: 

ALTER TABLE Customer DROP CONSTRAINT Con_First;

SQL Server: 

ALTER TABLE Customer DROP CONSTRAINT Con_First;

Frequently Asked Questions

Q: Why does MySQL use DROP INDEX instead of DROP CONSTRAINT to remove a UNIQUE constraint?
A: In MySQL, UNIQUE constraints are internally implemented as indexes. Therefore, to remove a UNIQUE constraint you must use DROP INDEX rather than DROP CONSTRAINT, which is the keyword used by Oracle and SQL Server.
Q: How do I find the name of a constraint so I can drop it?
A: In MySQL, use SHOW CREATE TABLE table_name; to see constraint names. In Oracle, query the USER_CONSTRAINTS data dictionary view. In SQL Server, query sys.key_constraints or sys.check_constraints.
Q: Does dropping a constraint affect the data in the table?
A: No. Dropping a constraint only removes the rule enforcing data integrity — it does not delete any rows or columns. However, after the constraint is removed, the database will no longer prevent violations.
Q: Can I drop a PRIMARY KEY constraint with ALTER TABLE?
A: Yes. In MySQL: ALTER TABLE Customer DROP PRIMARY KEY;. In Oracle/SQL Server: ALTER TABLE Customer DROP CONSTRAINT constraint_name;

Related Topics

Next: SQL NULL

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact