MySQL DROP CONSTRAINT Foreign Key, Unique, Check, and Primary Key Syntax

A migration script written against another engine ends with ALTER TABLE ... DROP CONSTRAINT, and you need to know whether MySQL takes it. MySQL 8.4 does, and so has every release since 8.0.19. MySQL also keeps a statement of its own for each constraint type:

DROP CONSTRAINT does the same job for a named foreign key, unique constraint or check constraint. As of MySQL 8.0.19, ALTER TABLE permits "more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name".

NOT NULL is the row with no DROP of any kind, because it is not a named constraint. You redefine the column without it, and the definition has to repeat every attribute the column keeps: attributes not specified for the new definition "are not carried forward".

The examples below run against these tables:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    sku        VARCHAR(50) NOT NULL,
    version    INT NOT NULL,
    CONSTRAINT uq_products_sku UNIQUE (sku)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary      DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_salary_positive CHECK (salary > 0)
);

When you need the type-specific statement

Three cases send you back to the right-hand column of that table:

• the server predates MySQL 8.0.19, the release where DROP CONSTRAINT arrived
• two constraints of different types carry the same name
• the constraint is a primary key, whose name is PRIMARY in every table and which DROP PRIMARY KEY removes without naming

The shared name is the case worth spelling out. MySQL gives each constraint type its own namespace per schema, so a check constraint and a foreign key may both be called chk_salary_positive. The manual says that "when multiple constraints have the same name, DROP CONSTRAINT and ADD CONSTRAINT are ambiguous and an error occurs", and that constraint-specific syntax has to be used there instead.

Version matters for check constraints only. As of MySQL 8.0.16, CREATE TABLE "permits the core features of table and column CHECK constraints, for all storage engines". Earlier releases accepted the clause and, in the manual's words, it "is parsed and ignored", so on an older server there is no check constraint in the schema to drop.

Find existing constraint names

DROP CONSTRAINT and DROP FOREIGN KEY both want the constraint name, and a name guessed from the column is the usual reason the statement comes back with this:

ERROR 1091 (42000): Can't DROP 'uq_products_sku'; check that column/key exists

Ask the data dictionary what the table really has:

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

information_schema.key_column_usage, filtered the same way with referenced_table_name IS NOT NULL, adds the columns each foreign key spans and the table it points at.

SHOW CREATE TABLE answers the same question in one line of typing, and prints the table as MySQL stored it rather than as the migration script wrote it. It is also the manual's answer for a foreign key declared without a CONSTRAINT name, whose name MySQL generates internally.

Drop a foreign key

ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

Referential enforcement stops there, but the index does not go with it. MySQL "requires indexes on foreign keys and referenced keys" and creates that index on the referencing table when none exists, so the drop leaves an index behind, still maintained on every insert and still visible to the planner. When nothing else needs it, remove both in one statement:

ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer,
DROP INDEX fk_orders_customer;

The second clause names an index, not a constraint, so check the index name in information_schema.statistics before you write that line.

Order matters on the parent side. The manual lists "dropping an index required by a foreign key constraint" among the operations that disabling foreign key checks does not let through, and says the constraint has to go before the index.

Drop a unique constraint

MySQL stores a unique constraint as a unique index, so it comes off as an index:

ALTER TABLE products
DROP INDEX uq_products_sku;

ALTER TABLE products DROP CONSTRAINT uq_products_sku; removes the same object, because the name resolves to the unique constraint. Either statement makes the column accept duplicates again, since the constraint and the index are one thing.

To find the name when the migration did not record it, list the unique indexes on the table:

SELECT index_name, column_name
FROM information_schema.statistics
WHERE table_schema = 'shop'
  AND table_name = 'products'
  AND non_unique = 0;

A UNIQUE clause written without a CONSTRAINT name is where that query earns its place. MySQL then names the index after the first indexed column, "with an optional suffix (_2, _3, ...) to make it unique", so the index behind an unnamed UNIQUE (sku) is called sku.

Drop a check constraint

ALTER TABLE employees
DROP CHECK chk_salary_positive;

DROP CONSTRAINT chk_salary_positive removes the same constraint, and DROP CHECK is the MySQL spelling rather than the portable one.

Read the expression the constraint enforces before it goes, because the name rarely carries all of it. information_schema.check_constraints holds one row per check constraint, and its CHECK_CLAUSE column is "the expression that specifies the constraint condition":

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'shop';

The clause comes back as the server normalized it.

Dropping is not the only way to get a check out of the way. ALTER TABLE employees ALTER CHECK chk_salary_positive NOT ENFORCED; leaves the constraint in the schema and stops it being applied, which is what a data load that would violate the rule wants: ENFORCED switches it back on afterwards. Drop the constraint when the rule itself is wrong.

Drop or replace a primary key

ALTER TABLE products
DROP PRIMARY KEY;

Three things stop that statement:

• a foreign key in another table that references the key, because the index a foreign key requires cannot be dropped while the constraint exists
• the sql_require_primary_key system variable, which turns any primary key drop into an error while it is enabled
• an AUTO_INCREMENT column that no other index covers, since MySQL allows "only one AUTO_INCREMENT column per table, it must be indexed"

The third case fails like this:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Replacing the key rather than dropping it is the way through, and the table is never left without one:

ALTER TABLE products
DROP PRIMARY KEY,
ADD PRIMARY KEY (product_id, version);

Disable foreign key checks temporarily

Emptying related tables fails on the foreign keys between them: TRUNCATE TABLE fails for an InnoDB table "if there are any FOREIGN KEY constraints from other tables that reference the table". foreign_key_checks is enabled by default and has both global and session scope, so one session can switch it off for the length of a load:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE orders;
TRUNCATE TABLE customers;

SET FOREIGN_KEY_CHECKS = 1;

TRUNCATE TABLE removes every row in the table and cannot be rolled back, so check which database the session is connected to first.

Switching the variable back on validates nothing: enabling it "does not trigger a scan of table data", so rows written while the checks were off are never checked for consistency. Orphan rows created during the load stay orphan rows, so check the load with a LEFT JOIN ... WHERE parent_key IS NULL before you hand the database back.

Drop constraints visually in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

A diagram shows which tables reference the one you are about to change, faster than reading table definitions one at a time. The steps in DbSchema:

1. click Connect to Database and pick MySql
2. fill in the Database User and Password, and DbSchema reverse-engineers the schema into a diagram with a line for every foreign key
3. double-click a table header to open the Table Dialog, which holds the table's columns, indexes, and foreign keys
4. remove the constraint there
5. open Schema, then Synchronize Model with Database to read the generated SQL before it runs

What removing the constraint changes depends on the mode the connection is in. Connected, DbSchema executes the statement against MySQL immediately and lists it in the SQL History pane. Disconnected, it changes only the .dbs design model file on your computer, and the database keeps the constraint until you synchronize. The statements in the synchronization dialog are editable before you click Execute.

Download DbSchema at https://dbschema.com/download.html, connect to your MySQL server, and look at the diagram before the next constraint comes off. Connecting, reverse-engineering, and the diagrams are in the free Community Edition; saving the model file and the synchronization dialog are in Pro.