# SQL DROP CONSTRAINT: Remove a Constraint from a Table

- URL: https://betteranalyst.com/sql/drop-constraint

SQL guide · Constraints

`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

- [SQL ADD CONSTRAINT](https://betteranalyst.com/sql/add-constraint)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL PRIMARY KEY](https://betteranalyst.com/sql/primary-key)
- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)
- [SQL UNIQUE](https://betteranalyst.com/sql/unique)
- [SQL DROP DEFAULT](https://betteranalyst.com/sql/drop-default)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)