📋

Description

MySQL Error 1215, "Cannot add foreign key constraint," indicates that your SQL statement failed to successfully create or alter a foreign key relationship between two tables. This error typically occurs when the conditions required for establishing referential integrity are not met, preventing the database from enforcing the desired relationship.

💬

Error Message

Cannot add foreign key constraint

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

The data type, length, or sign (e.g., signed vs. unsigned integer) of the foreign key column does not exactly match the data type, length, or sign of the referenced primary or unique key column.

⚠️

Collation Mismatch

For character string columns, the character set and collation of the foreign key column must precisely match those of the referenced column in the parent table.

⚠️

Missing Referenced Index

The columns in the parent table that the foreign key references must be indexed (either a PRIMARY KEY or a UNIQUE KEY) for the constraint to be successfully established.

⚠️

Non-Existent Referenced Column/Table

The foreign key constraint attempts to reference a table or column that does not exist or is misspelled in the database schema.

🛠️

Solutions

5 solutions available

1. Match Column Types Exactly medium

FK and referenced column must have identical types

1

Check parent table column definition

SHOW CREATE TABLE parent_table;

2

Ensure exact type match

-- Parent: id INT UNSIGNED
-- Child must match: user_id INT UNSIGNED (not INT, not BIGINT)

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED,  -- Must match users.id type
  FOREIGN KEY (user_id) REFERENCES users(id)
);

3

Common mismatches to check

-- INT vs INT UNSIGNED
-- BIGINT vs INT
-- VARCHAR(255) vs VARCHAR(100)
-- Different charsets (utf8 vs utf8mb4)

2. Ensure Referenced Column is Indexed easy

Parent column must have PRIMARY KEY or UNIQUE index

1

Check indexes on parent table

SHOW INDEX FROM parent_table;

2

Add index if missing

-- FK must reference PRIMARY KEY or UNIQUE column
ALTER TABLE parent_table ADD PRIMARY KEY (id);
-- Or
ALTER TABLE parent_table ADD UNIQUE INDEX (id);

3. Use Same Storage Engine easy

Both tables must use InnoDB

1

Check table engines

SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database';

2

Convert MyISAM to InnoDB

ALTER TABLE your_table ENGINE = InnoDB;

4. Match Character Set and Collation medium

Text columns must have matching charset

1

Check column charset

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

2

Fix charset mismatch

-- Make both columns use same charset
ALTER TABLE child_table 
MODIFY COLUMN parent_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. Check Parent Table Exists easy

Referenced table must exist first

1

Verify parent table exists

SHOW TABLES LIKE 'parent_table';

2

Create tables in correct order

-- Create parent first
CREATE TABLE users (id INT PRIMARY KEY);

-- Then child with FK
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

🔗

Related Errors

5 related errors

MySQL Error 1215: Foreign Key Constraint Failure