Back to all posts

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:

ConstraintStatementMySQL version
Foreign keyALTER TABLE tbl DROP FOREIGN KEY name;5.7 and later
UniqueALTER TABLE tbl DROP INDEX name;5.7 and later
CheckALTER TABLE tbl DROP CHECK name;8.0.16 and later
Primary keyALTER TABLE tbl DROP PRIMARY KEY;5.7 and later
Any of the fourALTER TABLE tbl DROP CONSTRAINT name;8.0.19 and later
NOT NULLALTER 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:

What each statement removes from orders: DROP PRIMARY KEY removes the index PRIMARY, DROP INDEX removes the unique constraint uq_orders_order_no, DROP FOREIGN KEY removes the foreign key fk_orders_customer and leaves its index, and DROP CHECK removes the check chk_orders_amount

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_NAMECONSTRAINT_TYPE
chk_orders_amountCHECK
fk_orders_customerFOREIGN KEY
PRIMARYPRIMARY KEY
uq_orders_order_noUNIQUE

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 KEY and DROP INDEX: ERROR 1091 (42000): Can't DROP 'customer_id'; check that column/key exists
  • DROP 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_idorder_no
10A-10
11A-11
12A-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_NAMECHECK_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 customers fails with ERROR 1553 (HY000): Cannot drop index 'PRIMARY': needed in a foreign key constraint, even with foreign key checks off.
  • The variable sql_require_primary_key set to ON, which makes CREATE TABLE and ALTER TABLE require a primary key. The drop fails with ERROR 3750 (HY000): Unable to create or change a table without a primary key, followed by a longer explanation.
  • An AUTO_INCREMENT column with no other index, because MySQL allows "only one AUTO_INCREMENT column 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_idcustomer_id
123

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.

  1. Connect DbSchema to your MySQL database. DbSchema reads the schema and draws it as a diagram.
  2. Double-click the header of orders on the diagram to open the Edit Table dialog.
  3. Open the Primary Key / Unique / Indexes, Foreign Keys, or Check Constraints tab, whichever holds the constraint.
  4. Select the constraint, press Drop, then OK.
The Foreign Keys tab of the Edit Table dialog for orders in DbSchema, with fk_orders_customer selected and the Drop button

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.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download