MySQL Error 1215 - Cannot Add Foreign Key Constraint

What It Means

MySQL error 1215 (ER_CANNOT_ADD_FOREIGN) means “Cannot add foreign key constraint.” MySQL was unable to create a foreign key relationship between two tables. This is usually due to a mismatch between the columns being linked.

Common Causes

• Data type mismatch between the foreign key and referenced column
• Referenced column doesn’t have an index
• Referenced table doesn’t exist
• Tables using different storage engines (MyISAM vs InnoDB)
• Character set or collation mismatch between columns
• Column signedness mismatch (UNSIGNED vs signed)
• Referencing a non-unique column without an index

How to Fix

Check data types match exactly

-- The data types must match EXACTLY
-- Bad: INT referencing BIGINT
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT,  -- Mismatch! users.id is INT
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Good: matching data types
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED,  -- Must match users.id type exactly
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Check column types in both tables
DESCRIBE users;
DESCRIBE orders;

Ensure both tables use InnoDB

-- Check table engines
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

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

Ensure referenced column has an index

-- The referenced column must be indexed (usually a PRIMARY KEY or UNIQUE)
SHOW INDEX FROM users;

-- Add an index if missing
ALTER TABLE users ADD INDEX idx_email (email);

-- Then create the foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_email) REFERENCES users(email);

Fix character set and collation mismatch

-- Check character sets
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
AND COLUMN_NAME IN ('user_id', 'id');

-- Fix mismatched character sets on VARCHAR foreign keys
ALTER TABLE orders
MODIFY COLUMN user_code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Fix signedness mismatch

-- Bad: one is UNSIGNED, the other is signed
-- users.id is INT UNSIGNED AUTO_INCREMENT
-- orders.user_id is INT (signed)

-- Fix: make both match
ALTER TABLE orders MODIFY user_id INT UNSIGNED;

Debug the issue

-- Get detailed error information
SHOW ENGINE INNODB STATUS;
-- Look for the "LATEST FOREIGN KEY ERROR" section

-- Temporarily disable foreign key checks (for debugging)
SET FOREIGN_KEY_CHECKS = 0;
-- Run your ALTER TABLE
SET FOREIGN_KEY_CHECKS = 1;

Related Errors

• MySQL 1146 - Table doesn’t exist: The referenced table is missing.
• MySQL 1451 - Foreign key constraint prevents deletion: Constraint exists but blocks a delete.
• MySQL 1062 - Duplicate entry: Another type of constraint violation.