SQL guide · Constraints

SQL DROP CONSTRAINT: Remove a Constraint from a Table

DROP CONSTRAINT removes a named rule from a table using ALTER TABLE. You might need it when a business rule changes, when you want to replace a constraint with a corrected version, when a bulk data load is blocked by a check, or before changing the data type of a column that a key depends on. Dropping a constraint does not delete any data; it only stops the database from enforcing that rule in the future.

To drop a constraint you need to know its name. If you named it when you created it, that is easy. If you did not, the database generated a name, and you must look it up in the system catalog, for example information_schema.table_constraints in most databases, sys.objects and sys.key_constraints in SQL Server, or user_constraints in Oracle. This is the main reason it pays to name every constraint at creation time.

Be aware of dependencies. A primary key or unique constraint that is referenced by a foreign key in another table cannot be dropped until that foreign key is removed, unless you use CASCADE where supported. Dropping a primary key also removes its underlying index, which can make queries slower, and dropping a check or foreign key means bad data can enter again. Treat it as a change to data integrity, not just to structure, and take a backup or run it in a migration that can be reversed.

Basic syntax

-- Standard SQL, PostgreSQL, SQL Server, Oracle
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

-- MySQL (type specific)
ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name DROP FOREIGN KEY fk_name;
ALTER TABLE table_name DROP INDEX unique_name;
ALTER TABLE table_name DROP CHECK check_name;

constraint_name must match the existing constraint exactly. MySQL historically needed a separate form for each constraint type, while the others use one generic form. Some databases accept IF EXISTS and CASCADE options.

SQL DROP CONSTRAINT examples

Drop a check constraint

The salary rule is no longer wanted, so it is removed by name. After this runs, negative or zero salaries are accepted again. Existing rows are untouched.

ALTER TABLE employees
DROP CONSTRAINT chk_salary_positive;

Drop a foreign key

This removes the link between orders and customers. The customer_id column and its data stay in place, but the database no longer checks that each value exists in the parent table. The MySQL form is shown in a comment.

ALTER TABLE orders
DROP CONSTRAINT fk_orders_customer;

-- MySQL
-- ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

Drop a unique constraint or primary key

A unique constraint uses the generic syntax in most databases. MySQL treats it as an index. For a primary key that other tables reference, drop those foreign keys first, or use CASCADE in PostgreSQL.

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE customers
DROP CONSTRAINT uq_customers_email;

-- MySQL
-- ALTER TABLE customers DROP INDEX uq_customers_email;

-- PostgreSQL, also dropping dependent foreign keys
ALTER TABLE customers
DROP CONSTRAINT pk_customers CASCADE;

Find an unnamed constraint first

If the name is unknown, look it up in the catalog before dropping. This standard query works in PostgreSQL, MySQL and SQL Server.

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';

Where SQL dialects differ

PostgreSQL: Uses DROP CONSTRAINT [IF EXISTS] name [CASCADE | RESTRICT]. Default is RESTRICT, which refuses if other objects depend on it. Works for primary key, unique, foreign key, check and exclusion constraints.

MySQL: Before 8.0.19 each type needed its own form: DROP PRIMARY KEY, DROP FOREIGN KEY name, DROP INDEX name for unique, and DROP CHECK name for checks (from 8.0.16). From 8.0.19 the generic DROP CONSTRAINT name works for all of them.

SQL Server: Uses ALTER TABLE t DROP CONSTRAINT name, and from SQL Server 2016 you can add IF EXISTS. The same statement drops default constraints. Several can be listed, separated by commas.

SQLite: ALTER TABLE DROP CONSTRAINT is not supported. Recreate the table without the constraint and copy the data across, or drop a unique index with DROP INDEX.

Oracle: Uses ALTER TABLE t DROP CONSTRAINT name, with CASCADE to remove dependent foreign keys. DROP PRIMARY KEY and DROP UNIQUE (columns) are also accepted when you do not know the name.

Common mistakes

  • Guessing the constraint name. Unnamed constraints get system-generated names, so query the catalog to find the real one.
  • Trying to drop a primary key that is referenced by foreign keys. Remove or cascade those first, or the statement fails.
  • Using the generic syntax on an older MySQL version, where the type-specific forms are required.
  • Forgetting that NOT NULL and DEFAULT are usually not dropped this way. They are changed through ALTER COLUMN or MODIFY.
  • Dropping a rule and leaving invalid data in place, which then blocks you from adding the rule back later.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator