ERROR 1215 (HY000): Cannot add foreign key constraint

What it means

A foreign key can only be created when the child column and the parent column it points at are compatible, and the parent has an index the server can use to check values quickly. If not, the CREATE TABLE or ALTER TABLE fails. Which error you see depends on the server:

If instead you see Cannot truncate a table referenced in a foreign key constraint (1701) or Cannot drop table … referenced by a foreign key constraint (3730), the foreign key already exists and is protecting a parent table: see Cannot delete or update a parent row.

Common causes

1. Integer types that don’t match exactly: bigint against int, or int unsigned against int. Both columns must have the same type, size and sign: an id created as bigint unsigned can’t be referenced by a column written as int.
2. Different character sets or collations on string columns: utf8mb4_general_ci against utf8mb4_bin, or a table created before the server’s default changed. (Lengths may differ: varchar(20) can reference varchar(50).)
3. No suitable index on the parent column. MySQL 8.4 wants a primary key or unique index; MariaDB accepts any index that starts with the column.
4. The parent table doesn’t exist yet (tables created in the wrong order) or isn’t InnoDB (MyISAM has no foreign keys).
5. ON DELETE SET NULL on a NOT NULL column.
6. Temporary tables or virtual generated columns, which can’t take part in foreign keys.

How to fix it

Compare the two columns

SELECT table_name, column_name, column_type, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND (table_name, column_name) IN (('parents', 'id'), ('parents', 'code'),
                                    ('kid_fix', 'parent_id'), ('kid_fix', 'parent_code'));

TABLE_NAME	COLUMN_NAME	COLUMN_TYPE	CHARACTER_SET_NAME	COLLATION_NAME
kid_fix	parent_id	bigint	NULL	NULL
kid_fix	parent_code	varchar(20)	utf8mb4	utf8mb4_general_ci
parents	id	int	NULL	NULL
parents	code	varchar(20)	utf8mb4	utf8mb4_bin

Every attribute except string length must match.

Make the child column match

Usually it’s the child you change, since the parent’s key is used elsewhere:

ALTER TABLE kid_fix MODIFY parent_id int;
ALTER TABLE kid_fix MODIFY parent_code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Restate NOT NULL and any default in MODIFY, or they’re dropped. Changing a column’s type rewrites the table; see changing a column type.

Give the parent a unique index

ALTER TABLE parents ADD UNIQUE KEY uq_parents_code (code);

See adding an index for how that runs on a large table. A foreign key to a non-unique column is deprecated in MySQL 8.4: it’s refused unless restrict_fk_on_non_standard_key is turned off, and then allowed with a warning.

Create tables in order, and use InnoDB

Create the parent before the child, or add the foreign keys after all the tables exist. Check the engines with SHOW TABLE STATUS WHERE Name IN ('parents', 'kid_fix'); and convert a MyISAM parent with ALTER TABLE parents ENGINE=InnoDB.

Watch for the opposite case too: a MyISAM child accepts FOREIGN KEY in CREATE TABLE without error on both servers, creates only an index, and enforces nothing.

On MariaDB, ask for the reason

Right after the error, in the same session:

SHOW WARNINGS;

SHOW ENGINE INNODB STATUS also keeps the last one, under LATEST FOREIGN KEY ERROR.

Reproduce it

On MySQL 8.4.11, with parents (id int PRIMARY KEY, code varchar(20) COLLATE utf8mb4_bin, ref int, KEY (ref)):

ERROR 3780 (HY000): Referencing column 'parent_id' and referenced column 'id' in foreign key constraint 'fk_kid_type' are incompatible.

That was parent_id bigint; int unsigned and a utf8mb4_general_ci string column gave the same 3780. Referencing code (no index) or ref (a non-unique index):

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'fk_kid_noidx' in the referenced table 'parents'

With SET SESSION restrict_fk_on_non_standard_key = OFF, the unindexed column gave 1822 and the non-unique one was accepted with a warning:

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk_kid_noidx' in the referenced table 'parents'
Warning	6124	Foreign key 'fk_kid_nonuniq' refers to non-unique key or partial key. This is deprecated and will be removed in a future release.

The other causes:

ERROR 1824 (HY000): Failed to open the referenced table 'parentz'
ERROR 1830 (HY000): Column 'parent_id' cannot be NOT NULL: needed in a foreign key constraint 'fk_kid_setnull' SET NULL
ERROR 1824 (HY000): Failed to open the referenced table 'myparent'
ERROR 1215 (HY000): Cannot add foreign key constraint
ERROR 3733 (HY000): Foreign key 'fk_kid_gen' uses virtual column 'parent_id' which is not supported.

(A missing parent table, SET NULL on NOT NULL, a MyISAM parent, a temporary child table, a virtual generated column.)

MariaDB 11.4.13 answered every one of these, except the non-unique index, which it accepted, with the same error:

ERROR 1005 (HY000): Can't create table `seo_mysql`.`kid_type` (errno: 150 "Foreign key constraint is incorrectly formed")

SHOW WARNINGS then gave the reason:

Warning	150	Create  table `seo_mysql`.`kid_type` with foreign key `fk_kid_type` constraint failed. Field type or character set for column 'parent_id' does not match referenced column 'id'.
Error	1005	Can't create table `seo_mysql`.`kid_type` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning	1215	Cannot add foreign key constraint for `kid_type`

For a missing parent table it said Referenced table … not found in the data dictionary; for SET NULL, You have defined a SET NULL condition but column 'parent_id' is defined as NOT NULL. For a collation mismatch it said, misleadingly, There is no index in the referenced table where the referenced columns appear as the first columns, so compare collations even when MariaDB blames the index. ALTER TABLE kid_fix ADD CONSTRAINT … also reports Can't create table, though the table exists. After the MODIFY and ADD UNIQUE KEY fixes above, both foreign keys were created on both servers.

In Inlet

Inlet’s structure editor works with columns, indexes and constraints, shows the DDL before it runs, and warns when a change rewrites or scans the table. When the statement fails, the error is shown with a hint, and with your own Anthropic API key Ask Claude (⌘L) can fix it from the schema.