1217ERRORTier 1 — Safe✅ HIGH confidence

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

Category: Integrity Constraint ViolationVersions: All MariaDB / MySQL versions

What this means

Error 1217 (SQLSTATE 23000) is raised when a DELETE or UPDATE on a parent table would leave child rows with orphaned foreign key values. InnoDB blocks the parent modification to preserve referential integrity.

Why it happens

1Deleting a parent row that has child rows referencing it without ON DELETE CASCADE
2Updating a parent row's primary key to a different value while child rows reference the old value without ON UPDATE CASCADE

How to reproduce

A parent row is deleted while child rows still reference it.

trigger — this will ERROR

-- Using the customers/orders setup from error 1216:
INSERT INTO customers VALUES (1);
INSERT INTO orders VALUES (1, 1);

DELETE FROM customers WHERE id = 1;  -- order 1 still references customer 1

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Fix 1: Delete child rows before the parent row

When the children should also be deleted.

fix

DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;

Why this works

Removing the child rows first means there are no more FK references to the parent row, allowing the parent to be deleted without violating the constraint.

Fix 2: Add ON DELETE CASCADE to the FK definition

When child rows should always be deleted with their parent.

fix

ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE;

Why this works

ON DELETE CASCADE instructs InnoDB to automatically delete all child rows when the referenced parent row is deleted, preventing 1217.

What not to do

✗

Use SET foreign_key_checks=0 to delete the parent while children remain

Why it's wrong: This leaves orphaned child rows in the database. Subsequent JOINs to the deleted parent will silently return no rows for those children, hiding data corruption.

Version notes

All versionsON DELETE CASCADE and ON DELETE SET NULL are the two common options for handling parent deletion. ON DELETE RESTRICT (the default) produces error 1217.

Sources

📚 Official docs: https://mariadb.com/kb/en/foreign-keys/#actions

🔧 Source ref: MariaDB Server error code 1217 / ER_ROW_IS_REFERENCED

📖 Further reading: MariaDB foreign key ON DELETE actions

Confidence assessment

✅ HIGH confidence

Stable and well-documented.