📋

Description

MariaDB Error 1217 indicates an attempt to delete or update a row in a parent table that is referenced by one or more rows in a child table. This error occurs when a foreign key constraint is violated, designed to preserve the integrity of your database relationships by preventing orphaned records.

💬

Error Message

Cannot delete or update a parent row: a foreign key constraint fails

🔍

Known Causes

4 known causes

⚠️

Existing Child Records

You are attempting to remove a record from a parent table, but there are still dependent records in a child table that reference it.

⚠️

Updating Referenced Primary Key

An update operation on the primary key (or unique key) of a parent table row is blocked because child table rows depend on its current value.

⚠️

Strict Foreign Key Constraint

The foreign key constraint is configured with a `RESTRICT` action (often the default behavior), preventing parent row modification if child rows exist.

⚠️

Application Logic Oversight

The application or script attempting the operation did not account for existing child records or the foreign key constraint's behavior.

🛠️

Solutions

3 solutions available

1. Delete Child Records First easy

Remove dependent rows before parent

1

Delete children before parent

-- Delete child records first
DELETE FROM orders WHERE user_id = 123;

-- Now delete parent
DELETE FROM users WHERE id = 123;

2. Use ON DELETE CASCADE medium

Automatically delete children with parent

1

Modify FK to cascade deletes

ALTER TABLE orders
DROP FOREIGN KEY fk_user_id,
ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

2

Or create table with CASCADE

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

3. Use ON DELETE SET NULL medium

Set child FK to NULL instead of deleting

1

Set FK to null on parent delete

ALTER TABLE orders
MODIFY user_id INT NULL,
DROP FOREIGN KEY fk_user_id,
ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL;

🔗

Related Errors

5 related errors

MariaDB Error 1217: Parent Row Update/Delete Blocked