📋

Description

This error indicates that you are attempting to delete or update a row in a 'parent' table that is currently referenced by one or more rows in a 'child' table. MariaDB prevents this operation to maintain referential integrity, ensuring that no orphaned child records are created.

💬

Error Message

Cannot delete or update a parent row: a foreign key constraint fails (%s)

🔍

Known Causes

4 known causes

⚠️

Referenced Parent Row Deletion

You attempted to delete a row from a parent table, but there are still dependent rows in a child table linked by a foreign key constraint.

⚠️

Referenced Parent Row Update

You tried to modify the primary key of a parent row, which would invalidate the foreign key references from existing child rows.

⚠️

Missing or Inadequate FK Actions

The foreign key constraint is defined without appropriate `ON DELETE` or `ON UPDATE` actions (e.g., `CASCADE`, `SET NULL`) to handle changes in the parent table.

⚠️

Unchecked Dependencies

The delete or update operation was executed without first verifying the existence of dependent child records, leading to the constraint violation.

🛠️

Solutions

3 solutions available

1. Temporarily Disable Foreign Key Checks easy

A quick way to bypass the constraint for a single operation.

1

Before executing your DELETE or UPDATE statement, temporarily disable foreign key checks for the current session.

SET FOREIGN_KEY_CHECKS = 0;

2

Execute your DELETE or UPDATE statement that is causing the error.

DELETE FROM parent_table WHERE id = 123;
-- or
UPDATE parent_table SET some_column = 'new_value' WHERE id = 123;

3

Immediately re-enable foreign key checks to maintain data integrity.

SET FOREIGN_KEY_CHECKS = 1;

2. Delete or Update Related Child Records First medium

The proper way to resolve the constraint by handling dependent data.

1

Identify the child table(s) and the specific rows that reference the parent row you intend to delete or update. The error message will often hint at the constraint name, which can help you pinpoint the tables involved.

SHOW CREATE TABLE child_table_name;

2

Delete or update the referencing rows in the child table(s). You might need to perform this operation on multiple child tables if the parent is referenced by several.

DELETE FROM child_table_name WHERE parent_id_column = 123;
-- or
UPDATE child_table_name SET parent_id_column = NULL WHERE parent_id_column = 123; -- If the child column allows NULLs

3

Once the child records are handled, you can safely delete or update the parent row.

DELETE FROM parent_table WHERE id = 123;
-- or
UPDATE parent_table SET some_column = 'new_value' WHERE id = 123;

3. Modify Foreign Key Constraint Behavior advanced

Configure the constraint to automatically handle dependent records.

1

Drop the existing foreign key constraint.

ALTER TABLE child_table_name DROP FOREIGN KEY constraint_name;

2

Re-create the foreign key constraint with an `ON DELETE` or `ON UPDATE` action. Common options include `CASCADE` (automatically delete/update child rows) or `SET NULL` (set the foreign key column in child rows to NULL).

ALTER TABLE child_table_name ADD CONSTRAINT constraint_name FOREIGN KEY (parent_id_column) REFERENCES parent_table(id) ON DELETE CASCADE ON UPDATE CASCADE;
-- or for SET NULL (if parent_id_column is nullable):
ALTER TABLE child_table_name ADD CONSTRAINT constraint_name FOREIGN KEY (parent_id_column) REFERENCES parent_table(id) ON DELETE SET NULL ON UPDATE SET NULL;

3

Now, deleting or updating the parent row will automatically affect the child rows according to the defined action.

DELETE FROM parent_table WHERE id = 123;

🔗

Related Errors

5 related errors

MariaDB Error 1451: Foreign Key Constraint Failure