📋

Description

This error indicates an attempt to insert or update a row in a 'child' table with a foreign key value that does not exist in the referenced 'parent' table's primary key. It occurs when MySQL's referential integrity rules are violated, preventing inconsistent data from being stored.

💬

Error Message

Cannot add or update a child row: a foreign key constraint fails

🔍

Known Causes

4 known causes

⚠️

Referenced Parent Row Missing

The record in the parent table that the child row is attempting to reference does not exist in the database.

⚠️

Incorrect Foreign Key Value

The foreign key value provided for the child row does not match any existing primary key value in the parent table.

⚠️

Data Type or Collation Mismatch

The data types, lengths, or character collations of the foreign key column in the child table and the primary key column in the parent table do not perfectly match.

⚠️

Uncommitted Parent Transaction

The parent row might have been inserted within an ongoing transaction but not yet committed, making it invisible to the foreign key check.

🛠️

Solutions

5 solutions available

1. Insert Parent Record First easy

Referenced row must exist before inserting child

1

Check if parent record exists

SELECT * FROM users WHERE id = 123;

2

Insert parent first, then child

-- Insert user first
INSERT INTO users (id, name) VALUES (123, 'John');

-- Then insert order referencing that user
INSERT INTO orders (user_id, total) VALUES (123, 99.99);

2. Fix Invalid Foreign Key Value easy

Ensure FK value matches existing parent

1

List valid parent values

SELECT id FROM users ORDER BY id;

2

Use valid FK value

-- Wrong: user_id 999 doesn't exist
-- INSERT INTO orders (user_id) VALUES (999);

-- Right: use existing user_id
INSERT INTO orders (user_id) VALUES (1);

3. Allow NULL for Optional Relationship easy

Use NULL when parent is optional

1

If FK column allows NULL, use NULL for no parent

-- If relationship is optional:
INSERT INTO orders (user_id, total) VALUES (NULL, 99.99);

2

Modify column to allow NULL

ALTER TABLE orders MODIFY user_id INT NULL;

4. Temporarily Disable FK Check medium

For bulk imports or data migration

1

Disable FK checks

SET FOREIGN_KEY_CHECKS = 0;

2

Perform your inserts

INSERT INTO orders (user_id, total) VALUES (123, 99.99);

3

Re-enable FK checks

SET FOREIGN_KEY_CHECKS = 1;

5. Fix UPDATE Foreign Key Value easy

New FK value must reference existing parent

1

When updating FK column

-- Wrong: user 999 doesn't exist
-- UPDATE orders SET user_id = 999 WHERE id = 1;

-- Right: update to existing user
UPDATE orders SET user_id = 2 WHERE id = 1;

🔗

Related Errors

5 related errors

MySQL Error 1216: Foreign Key Constraint Failure