How to Fix MySQL Error 1215: Cannot Add Foreign Key Constraint

Q: I set `FOREIGN_KEY_CHECKS = 0` but still get error 1215. Why?

FOREIGN_KEY_CHECKS = 0 only skips data consistency validation (orphan rows). MySQL still validates the constraint definition itself — column type match, index existence, charset match. Those checks cannot be bypassed.

Q: My child and parent columns look the same type in `SHOW CREATE TABLE` but the FK still fails.

Check UNSIGNED carefully — SHOW CREATE TABLE displays it, but it is easy to miss. Also check the charset/collation, which appears only in information_schema.columns . INT and INT UNSIGNED are not compatible for foreign keys.

Q: After adding the FK, existing queries slow down. Why?

InnoDB enforces the FK on every INSERT , UPDATE , and DELETE against the child table (lookup of parent row) and on every DELETE / UPDATE on the parent table (lookup of child rows). This adds index lookups on each write. Ensure both sides have indexes. If the overhead is unacceptable, consider enforcing referential integrity at the application layer instead.

ERROR 1215 (HY000): Cannot add foreign key constraint is raised by InnoDB when an ALTER TABLE ... ADD FOREIGN KEY or CREATE TABLE with a FOREIGN KEY clause fails validation. MySQL checks that the referencing and referenced columns are compatible — if any requirement is unmet, the constraint is rejected.

Impact

The ALTER TABLE or CREATE TABLE fails and is rolled back. No foreign key is created, but any other changes in the same ALTER TABLE statement may also be rolled back depending on ALGORITHM. In migration scripts, this typically halts the migration at that step, leaving subsequent migrations unapplied.

Common Causes

Data type mismatch: the child column and parent column are not exactly the same type, including unsigned/signed — INT vs INT UNSIGNED is a common source
Character set or collation mismatch: the child VARCHAR column uses a different charset or collation than the parent column
No index on the referenced column(s): the parent column must be indexed (PRIMARY KEY or UNIQUE index) — a plain non-unique index is not enough
No index on the referencing column(s): MySQL requires an index on the child column(s) for efficient enforcement; if none exists, MySQL creates one automatically, but sometimes cannot
Referenced table does not exist yet: when creating tables in a script where the parent table is defined after the child table
Parent and child tables use different storage engines: foreign keys require both tables to use InnoDB (or other FK-capable engines); MyISAM silently ignores FK definitions
The parent column allows NULL but the child column does not (not always an error, but certain combinations are)
ROW_FORMAT or innodb_file_per_table difference in rare legacy configurations

Troubleshooting and Resolution Steps

Get the full error detail from the InnoDB status:
```
SHOW ENGINE INNODB STATUS\G
```
Look for the LATEST FOREIGN KEY ERROR section — it provides more information than the generic 1215 message, including which specific requirement was not met.

Compare the data types of the referencing and referenced columns:

-- Child table
SELECT column_name, column_type, is_nullable, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'mydb' AND table_name = 'orders'
  AND column_name = 'customer_id';

-- Parent table
SELECT column_name, column_type, is_nullable, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'mydb' AND table_name = 'customers'
  AND column_name = 'id';

The column_type must match exactly. A common mismatch:

-- Parent: id INT UNSIGNED AUTO_INCREMENT (unsigned)
-- Child:  customer_id INT               (signed) — FAILS

-- Fix: make the child column match exactly
ALTER TABLE orders MODIFY customer_id INT UNSIGNED NOT NULL;

Fix character set and collation mismatches on VARCHAR foreign keys:

-- If the parent column is utf8mb4_unicode_ci:
ALTER TABLE order_tags MODIFY tag_name VARCHAR(50)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Verify the parent column is indexed:
```
SHOW INDEX FROM customers WHERE Column_name = 'id';
```
The referenced column must be the leftmost column of a PRIMARY KEY or UNIQUE index. A non-unique index is not sufficient for the parent side.

Ensure both tables use InnoDB:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'mydb'
  AND table_name IN ('orders', 'customers');

-- Convert if needed
ALTER TABLE orders ENGINE = InnoDB;

Fix table creation order in migration scripts. When a script creates child before parent:

-- Option 1: create tables in dependency order (parent first)

-- Option 2: create child without FK, then add FK after parent exists
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL
    -- no FK here
) ENGINE=InnoDB;

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id);

-- Option 3: disable FK checks during setup
SET FOREIGN_KEY_CHECKS = 0;
-- ... create all tables ...
SET FOREIGN_KEY_CHECKS = 1;

Temporarily disable foreign key checks for migrations (use with care):
```
SET SESSION FOREIGN_KEY_CHECKS = 0;

-- Run your DDL statements
CREATE TABLE ...;
ALTER TABLE ... ADD FOREIGN KEY ...;

SET SESSION FOREIGN_KEY_CHECKS = 1;
```
Always re-enable immediately after. Disabling FK checks allows creating constraints even when data is not yet consistent — verify data integrity manually before re-enabling.

Verify that the child column has (or can have) an index:

-- MySQL creates an index automatically on the FK column if none exists
-- but it may fail if the column is TEXT/BLOB (no key length specified):
ALTER TABLE orders
    ADD INDEX idx_customer_id (customer_id),
    ADD CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id);

Check for an existing FK that already uses the name you are trying to create:

SELECT constraint_name, table_name, column_name,
       referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'mydb'
  AND referenced_table_name IS NOT NULL;

Foreign key names must be unique within the database (not just the table).

Example: Full Working FK Setup

-- Parent table
CREATE TABLE customers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Child table — types and charset must match exactly
CREATE TABLE orders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,   -- must be INT UNSIGNED to match customers.id
    placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_customer_id (customer_id),  -- index required on child side
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Additional Information

ON DELETE RESTRICT (the default if omitted) prevents deleting a parent row that has child rows. ON DELETE CASCADE deletes child rows automatically. ON DELETE SET NULL sets the child FK column to NULL (the child column must be nullable).
InnoDB validates existing data when a FK constraint is added to an existing table. If orphan child rows exist (child rows whose FK value has no matching parent), the ALTER TABLE fails. Find and fix orphans first:
```
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
```
The FOREIGN_KEY_CHECKS variable is session-scoped and resets to ON after the session ends. Leaving it OFF in a persistent connection (from a pool) will silently skip FK enforcement for all subsequent statements from that connection.
MySQL 8.0 improved the FK error messages in SHOW ENGINE INNODB STATUS to include the specific reason for rejection.

Frequently Asked Questions

Q: I set FOREIGN_KEY_CHECKS = 0 but still get error 1215. Why? A: FOREIGN_KEY_CHECKS = 0 only skips data consistency validation (orphan rows). MySQL still validates the constraint definition itself — column type match, index existence, charset match. Those checks cannot be bypassed.

Q: My child and parent columns look the same type in SHOW CREATE TABLE but the FK still fails. A: Check UNSIGNED carefully — SHOW CREATE TABLE displays it, but it is easy to miss. Also check the charset/collation, which appears only in information_schema.columns. INT and INT UNSIGNED are not compatible for foreign keys.

Q: Can I have a FK on a TEXT or BLOB column? A: No. TEXT and BLOB columns cannot have a plain index without a key prefix length. Since indexes on prefix lengths do not uniquely identify a value, InnoDB does not allow FKs on TEXT/BLOB columns. Use VARCHAR with an appropriate length instead.

Q: Can I reference a non-unique column in the parent table? A: No. The referenced column(s) must form a PRIMARY KEY or UNIQUE KEY. This ensures that each FK value in the child table maps to exactly one row in the parent.

Q: After adding the FK, existing queries slow down. Why? A: InnoDB enforces the FK on every INSERT, UPDATE, and DELETE against the child table (lookup of parent row) and on every DELETE/UPDATE on the parent table (lookup of child rows). This adds index lookups on each write. Ensure both sides have indexes. If the overhead is unacceptable, consider enforcing referential integrity at the application layer instead.