📋

Description

This error indicates that you are attempting to insert or update a row in a 'child' table that references a non-existent value in its 'parent' table's primary key. It occurs when a foreign key constraint is violated, ensuring referential integrity between related tables.

💬

Error Message

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

🔍

Known Causes

4 known causes

⚠️

Missing Parent Row

The foreign key in the child table references a primary key value that does not exist in the parent table.

⚠️

Mismatched Data Types

The data type or value being inserted into the child table's foreign key column does not match the parent table's primary key column.

⚠️

Typographical Error

A simple typo or an incorrect value was provided in the INSERT or UPDATE statement for the foreign key column.

⚠️

Parent Row Deleted

The referenced parent row was deleted from the parent table without a CASCADE DELETE rule before the child row was added or updated.

🛠️

Solutions

3 solutions available

1. Insert Parent Record First easy

Create referenced row before child

1

Insert parent before child

-- Wrong order:
INSERT INTO orders (user_id, total) VALUES (999, 100);  -- User 999 doesn't exist!

-- Correct order:
INSERT INTO users (id, name) VALUES (999, 'John');  -- Create parent first
INSERT INTO orders (user_id, total) VALUES (999, 100);  -- Now child can reference it

2. Verify Parent Record Exists easy

Check before inserting child

1

Check if parent exists

SELECT id FROM users WHERE id = 999;

2

Use INSERT with subquery to validate

INSERT INTO orders (user_id, total)
SELECT 999, 100
WHERE EXISTS (SELECT 1 FROM users WHERE id = 999);

3. Temporarily Disable FK Checks medium

For bulk imports or migrations

1

Disable FK checks temporarily

SET FOREIGN_KEY_CHECKS = 0;

-- Your inserts here...

SET FOREIGN_KEY_CHECKS = 1;

🔗

Related Errors

5 related errors

MariaDB Error 1216: Foreign Key Constraint Failure