Home › Databases

ERROR 1215 (HY000): Cannot add foreign key constraint (MySQL) — how to fix

ERROR 1215 (HY000): Cannot add foreign key constraint
ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'fk_orders_user' are incompatible.
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk_orders_user' in the referenced table 'users'

Quick fix: Run SHOW ENGINE INNODB STATUS\G and read LATEST FOREIGN KEY ERROR. Make both columns the same type, length and signedness (e.g. both BIGINT UNSIGNED) and the same charset/collation, make the referenced column a PRIMARY KEY or indexed, create the parent table first, and use ENGINE=InnoDB.

What it means

A foreign key requires the child column and the parent column to be strictly compatible, and the parent column to be indexed. Error 1215 is the generic message; MySQL 8.0 often gives a more specific one (3780 for type mismatch, 1822 for missing index). Get the real reason:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST FOREIGN KEY ERROR section.

Cause 1: column types don't match exactly

Type, size and UNSIGNED must match. This is the most common cause — especially Laravel/Rails-style BIGINT UNSIGNED ids referenced by a plain INT:

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT,                                -- wrong: INT signed
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Fix the child column:

ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id);

Compare definitions with SHOW CREATE TABLE users; and SHOW CREATE TABLE orders;. (VARCHAR lengths may differ, but INT vs BIGINT or signed vs unsigned may not.)

Cause 2: string columns with different charset/collation

For VARCHAR/CHAR keys, both columns need the same character set and collation:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE() AND column_name IN ('code', 'country_code');

ALTER TABLE orders MODIFY country_code VARCHAR(2)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

This often appears when old utf8/latin1 tables meet new utf8mb4 ones.

Cause 3: referenced column isn't indexed

The parent column must be the leftmost column of an index (normally the PRIMARY KEY or a UNIQUE key):

ALTER TABLE users ADD UNIQUE INDEX ux_users_email (email);

For a composite foreign key, the columns must appear in the same order as in the parent index.

Cause 4: parent table doesn't exist yet (or is MyISAM)

Create tables in dependency order, or load dumps with checks off:

SET FOREIGN_KEY_CHECKS = 0;
-- CREATE TABLE ... / SOURCE dump.sql
SET FOREIGN_KEY_CHECKS = 1;

Both tables must be InnoDB:

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE();
ALTER TABLE users ENGINE=InnoDB;

Also check spelling and case of table names — on Linux, table names are case-sensitive by default.

Cause 5: ON DELETE SET NULL on a NOT NULL column

user_id BIGINT UNSIGNED NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL   -- invalid

Make the column nullable or use ON DELETE CASCADE / RESTRICT.

Cause 6: duplicate constraint name

Constraint names are unique per database. Reusing fk_user on two tables fails (error 1826 in newer versions, 1215/121 in older). Use names like fk_<table>_<column>.

Existing data violates the key

If the constraint definition is fine but existing rows reference missing parents, you'll get error 1452 instead — see Cannot add or update a child row.

Last updated 2026-11-09.