The 30-Second Fix: Check Column Types Match
Most of the time, error 1215 happens because you're trying to link two columns that don't have the exact same data type. MySQL is strict here — the child column and parent column must be identical in type, length, and unsigned status.
-- Example: if parent has INT UNSIGNED, child must be INT UNSIGNED
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);
-- Fails if users.id is INT UNSIGNED and orders.user_id is INT (signed)
Run DESCRIBE on both tables. Look at the Type column. If one says int(11) unsigned and the other says int(11), that's your problem. Fix the child column to match:
ALTER TABLE orders MODIFY user_id INT UNSIGNED NOT NULL;
This takes 30 seconds and fixes 70% of cases. Don't overthink it.
The 5-Minute Fix: Missing Index or Wrong Engine
If the types match and it still fails, the next common cause is that the referenced column (the parent) doesn't have an index. In InnoDB, the parent column in a foreign key must be indexed. Usually it's the primary key, so that's fine. But if you're referencing a non-primary column, double-check.
-- Add index to parent column if missing
ALTER TABLE users ADD INDEX idx_email (email);
Also verify both tables use InnoDB engine. MyISAM doesn't support foreign keys. Run:
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME IN ('users', 'orders');
If either says MyISAM, convert it:
ALTER TABLE orders ENGINE=InnoDB;
While you're there, check if the child column allows NULLs when the parent doesn't, or if default values differ. MySQL 8.0+ is stricter about this.
Quick checklist for moderate fix:
- Parent column has an index (PK or explicit index)
- Both tables are InnoDB
- Child column NOT NULL matches parent NOT NULL (or both allow NULL)
- No default value mismatch
The 15-Minute Fix: Charset, Collation, and Data Inconsistencies
If you're still stuck, the issue is sneaky. The parent and child columns may have different character sets or collations. This happens often when tables are created with different defaults. For string columns (VARCHAR, CHAR), both must use the same charset and collation.
-- Check collation
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
AND TABLE_NAME IN ('users', 'orders')
AND COLUMN_NAME IN ('email', 'user_id');
If they differ, change one to match:
ALTER TABLE orders MODIFY user_email VARCHAR(255)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Another rare but real cause: the parent table has data that references rows which don't exist in the child, or vice versa. MySQL checks existing data when adding a foreign key. Run this to find orphans:
SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
Delete or update those rows before retrying the constraint. Also check if the ON DELETE or ON UPDATE clause you're using conflicts with the table's existing triggers or constraints.
Final nuclear option
If nothing works, drop and recreate both tables with explicit definitions. This is heavy but sometimes the metadata gets corrupted. Export your data first.
CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX fk_user_idx (user_id),
CONSTRAINT fk_user FOREIGN KEY (user_id)
REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Import your data and the foreign key will work. This is the last resort, but it's saved me twice when all else seemed fine.
Bottom line: error 1215 is MySQL saying "I can't make this relationship work because of a conflict." The conflict is almost always type, index, engine, charset, or data. Work through them in this order and you'll fix it fast.