How to Fix MySQL Foreign Key Constraint Failed Error

In this tutorial, you'll learn about How to Fix MySQL Foreign Key Constraint Failed Error. We cover key concepts, practical examples, and best practices.

The Problem

You run an INSERT or ALTER TABLE and MySQL returns:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Or when creating a table:

ERROR 1215 (HY000): Cannot add foreign key constraint

MySQL enforces referential integrity and rejects the operation because the parent row does not exist, or the foreign key definition is invalid.

Quick Fix

1. Check that the parent row exists

A foreign key requires every child value to exist in the parent table:

-- Wrong — inserting an order for a non-existent user
INSERT INTO orders (user_id, total) VALUES (999, 50.00);

-- Right — insert the user first, or use a valid user_id
INSERT INTO users (id, name) VALUES (999, 'New User');
INSERT INTO orders (user_id, total) VALUES (999, 50.00);

Find orphaned rows before adding a foreign key:

SELECT DISTINCT orders.user_id
FROM orders
LEFT JOIN users ON orders.user_id = users.id
WHERE users.id IS NULL;

2. Check data types match

Foreign key columns must have the same data type and length:

-- Wrong — INT vs BIGINT
CREATE TABLE users (id BIGINT PRIMARY KEY);
CREATE TABLE orders (user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));

-- Right — matching types
CREATE TABLE users (id BIGINT PRIMARY KEY);
CREATE TABLE orders (user_id BIGINT, FOREIGN KEY (user_id) REFERENCES users(id));

3. Check the character set and collation

For string columns, both the charset and collation must match:

-- Wrong — different charset
CREATE TABLE users (email VARCHAR(100) CHARACTER SET utf8mb4 PRIMARY KEY);
CREATE TABLE orders (user_email VARCHAR(100) CHARACTER SET latin1,
  FOREIGN KEY (user_email) REFERENCES users(email));

-- Right — same charset
CREATE TABLE orders (user_email VARCHAR(100) CHARACTER SET utf8mb4,
  FOREIGN KEY (user_email) REFERENCES users(email));

4. Check the table engine

Both tables must use InnoDB (or another engine that supports foreign keys). MyISAM does not support foreign keys:

-- Wrong — MyISAM ignores FK definition
CREATE TABLE users (id INT PRIMARY KEY) ENGINE=MyISAM;

-- Right — InnoDB
CREATE TABLE users (id INT PRIMARY KEY) ENGINE=InnoDB;

Check existing engines:

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = 'mydb';

5. Verify the referenced column is indexed

The referenced column in the parent table must be a primary key or have a unique index:

-- Wrong — no index on referenced column
CREATE TABLE users (id INT);
CREATE TABLE orders (user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));

-- Right — add PRIMARY KEY or UNIQUE
CREATE TABLE users (id INT PRIMARY KEY);

Prevention

• Use consistent data types and the same character set across related tables.
• Always use InnoDB for tables with foreign key relationships.
• Check for orphaned rows before adding a foreign key constraint to existing data.
• Document all foreign key relationships in your schema design.

Common Mistakes with foreign key

1. Using return to exit a function early instead of wrapping a pure value in the monad
2. Mixing let bindings with <- bindings in do notation, producing type errors
3. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors

These mistakes appear frequently in real-world MYSQL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ