MySQL DROP CONSTRAINT Foreign Key, Unique, Check, and Primary Key Syntax
For developers who need to remove a constraint from a MySQL table and want the statement that MySQL accepts for it.
On this page
You need to remove a constraint from a MySQL table, and the statement you know from another database may not work in MySQL. Since MySQL 8.0.19, DROP CONSTRAINT removes a constraint of any type by its name. Each type also has its own statement. The last column gives the MySQL versions that accept each statement:
| Constraint | Statement | MySQL version |
|---|---|---|
| Foreign key | ALTER TABLE tbl DROP FOREIGN KEY name; | 5.7 and later |
| Unique | ALTER TABLE tbl DROP INDEX name; | 5.7 and later |
| Check | ALTER TABLE tbl DROP CHECK name; | 8.0.16 and later |
| Primary key | ALTER TABLE tbl DROP PRIMARY KEY; | 5.7 and later |
| Any of the four | ALTER TABLE tbl DROP CONSTRAINT name; | 8.0.19 and later |
| NOT NULL | ALTER TABLE tbl MODIFY col type NULL; | 5.7 and later |
Create two sample tables. The orders table has one constraint of each type:
CREATE TABLE customers (
customer_id INT PRIMARY KEY
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_no VARCHAR(10) NOT NULL,
amount INT NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
CONSTRAINT uq_orders_order_no UNIQUE (order_no),
CONSTRAINT chk_orders_amount CHECK (amount > 0)
);
INSERT INTO customers VALUES (1), (2);
INSERT INTO orders VALUES
(10, 1, 'A-10', 100),
(11, 2, 'A-11', 250);
MySQL stores the primary key and the unique constraint as indexes, so DROP PRIMARY KEY and DROP INDEX remove them. The foreign key needs an index on customer_id, so MySQL created one and gave it the constraint's name. DROP FOREIGN KEY removes the constraint and leaves that index in place:
Run the statements of each section on a freshly created sample table. Every statement ran on MySQL 8.0.29 and 9.1.0 and gave the same result on both. The results for MySQL 5.7 come from MySQL 5.7.44.
Find the constraint name
Every DROP statement except DROP PRIMARY KEY needs the constraint's name. The view information_schema.table_constraints lists the constraint names of a table:
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'shop'
AND table_name = 'orders'
ORDER BY constraint_type;
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
|---|---|
| chk_orders_amount | CHECK |
| fk_orders_customer | FOREIGN KEY |
| PRIMARY | PRIMARY KEY |
| uq_orders_order_no | UNIQUE |
Put your database's name in place of shop. SHOW CREATE TABLE orders prints the same names inside the table's definition.
If you create a constraint without a name, MySQL gives it one. If you created orders without the CONSTRAINT lines, MySQL would use these names:
- the foreign key
orders_ibfk_1, with an index named after its column,customer_id - the check
orders_chk_1 - the unique constraint
order_no, named after its first column
If you use a column name in place of the constraint name, the error depends on the statement:
DROP FOREIGN KEYandDROP INDEX:ERROR 1091 (42000): Can't DROP 'customer_id'; check that column/key existsDROP CHECK:ERROR 3821 (HY000): Check constraint 'amount' is not found in the table.DROP CONSTRAINT:ERROR 3940 (HY000): Constraint 'customer_id' does not exist.
Drop a foreign key
DROP FOREIGN KEY removes the constraint, and MySQL stops checking customer_id against customers:
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
The foreign key's index stays, and MySQL keeps updating it on every insert. SHOW CREATE TABLE orders still lists the index after the drop:
KEY `fk_orders_customer` (`customer_id`),
If no query needs that index, drop the constraint and the index in one statement:
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer,
DROP INDEX fk_orders_customer;
Don't drop the index first. While the foreign key exists, DROP INDEX fk_orders_customer fails, even with foreign key checks switched off:
ERROR 1553 (HY000): Cannot drop index 'fk_orders_customer': needed in a foreign key constraint
Drop a unique constraint
DROP INDEX removes a unique constraint. On 8.0.19 and later, DROP CONSTRAINT uq_orders_order_no does the same. After DROP INDEX, two orders can have the same order_no:
ALTER TABLE orders DROP INDEX uq_orders_order_no;
INSERT INTO orders VALUES (12, 1, 'A-10', 80);
SELECT order_id, order_no FROM orders;
| order_id | order_no |
|---|---|
| 10 | A-10 |
| 11 | A-11 |
| 12 | A-10 |
Before DROP INDEX, this INSERT failed with ERROR 1062 (23000): Duplicate entry 'A-10' for key 'orders.uq_orders_order_no'.
Drop a check constraint
DROP CHECK removes a check constraint. DROP CONSTRAINT chk_orders_amount does the same on 8.0.19 and later. Before you drop a check, read its rule from the view information_schema.check_constraints:
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'shop';
| CONSTRAINT_NAME | CHECK_CLAUSE |
|---|---|
| chk_orders_amount | (`amount` > 0) |
Then drop the check:
ALTER TABLE orders DROP CHECK chk_orders_amount;
You can also switch the check off for a while instead of dropping it, for example to load rows that it rejects:
ALTER TABLE orders ALTER CHECK chk_orders_amount NOT ENFORCED;
The check stays in the table. ALTER CHECK chk_orders_amount ENFORCED switches the check back on, but only if every row passes it. If a row has an amount of -5, ALTER CHECK fails with ERROR 3819 (HY000): Check constraint 'chk_orders_amount' is violated.
Drop or replace a primary key
DROP PRIMARY KEY takes no name, because a table has one primary key and MySQL always calls it PRIMARY:
ALTER TABLE orders DROP PRIMARY KEY;
On 8.0.19 and later, DROP CONSTRAINT `PRIMARY` works too, but only with the backticks. Without the backticks, the statement is a syntax error.
These things stop the drop:
- A foreign key in another table that references the key. Dropping the key of
customersfails withERROR 1553 (HY000): Cannot drop index 'PRIMARY': needed in a foreign key constraint, even with foreign key checks off. - The variable
sql_require_primary_keyset to ON, which makesCREATE TABLEandALTER TABLErequire a primary key. The drop fails withERROR 3750 (HY000): Unable to create or change a table without a primary key, followed by a longer explanation. - An
AUTO_INCREMENTcolumn with no other index, because MySQL allows "only oneAUTO_INCREMENTcolumn per table, it must be indexed".
For example, make order_id an AUTO_INCREMENT column and drop the primary key:
ALTER TABLE orders MODIFY order_id INT AUTO_INCREMENT;
ALTER TABLE orders DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
Replace the primary key in one statement instead: drop the old key and add a new one. The new key below starts with order_id, so the column stays indexed. The MySQL manual also allows this replacement when sql_require_primary_key is ON:
ALTER TABLE orders
DROP PRIMARY KEY,
ADD PRIMARY KEY (order_id, customer_id);
Remove a NOT NULL constraint
NOT NULL has no name, so no DROP statement removes it. Redefine the column as NULL with MODIFY instead:
ALTER TABLE orders MODIFY amount INT NULL;
In MODIFY, write out every attribute that the column keeps, such as its DEFAULT or COMMENT. MODIFY replaces the whole column definition. Attributes you leave out "are not carried forward", and MySQL gives no warning.
Older versions and shared names
MySQL 5.7 has no DROP CONSTRAINT and no DROP CHECK: on 5.7.44, both are syntax errors. MySQL 5.7 also stores no check constraint to drop. Before 8.0.16, the CHECK clause "is parsed and ignored", so on 5.7.44 MySQL accepts an amount of -5 in orders.
A table may have a unique key and a check with the same name. For that name, DROP CONSTRAINT fails with ERROR 3939 (HY000). Use the statement for the type instead, such as DROP CHECK.
Disable foreign key checks temporarily
To empty or reload tables that reference each other, switch the foreign key checks off instead of dropping the foreign key. For example, TRUNCATE TABLE customers fails while orders references customers:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`shop`.`orders`, CONSTRAINT `fk_orders_customer`)
The variable foreign_key_checks switches the checks off for your session:
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
TRUNCATE TABLE customers;
SET FOREIGN_KEY_CHECKS = 1;
Before you run TRUNCATE TABLE, check which database you're connected to. TRUNCATE TABLE commits at once and can't be rolled back.
Setting FOREIGN_KEY_CHECKS back to 1 "does not trigger a scan of table data". While the checks are off, you can write an order for a missing customer, and the order stays. A LEFT JOIN finds such orders:
SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO orders VALUES (12, 3, 'A-12', 80);
SET FOREIGN_KEY_CHECKS = 1;
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
| order_id | customer_id |
|---|---|
| 12 | 3 |
Customer 3 doesn't exist, and MySQL kept order 12 without an error.
Drop a constraint in DbSchema
DbSchema draws every foreign key as a line between two tables. Before you drop a foreign key, the diagram shows which tables reference the table you're changing.
- Connect DbSchema to your MySQL database. DbSchema reads the schema and draws it as a diagram.
- Double-click the header of
orderson the diagram to open the Edit Table dialog. - Open the Primary Key / Unique / Indexes, Foreign Keys, or Check Constraints tab, whichever holds the constraint.
- Select the constraint, press Drop, then OK.
When DbSchema is connected, clicking OK runs the ALTER TABLE in MySQL at once. The History pane lists that statement. Offline, the change stays in the DbSchema model until you synchronize the model with the database.
Download DbSchema at https://dbschema.com/download.html and connect it to your MySQL database, to see what references a table before you drop a constraint. Connecting, the diagram, editing tables and the SQL Editor are in the free Community Edition. Working offline and schema synchronization need Pro.

