EC
1217
MySQLERRORCommonIntegrity Constraint ViolationHIGH confidence

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

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
  1. 1Deleting a parent row that has child rows referencing it without ON DELETE CASCADE
  2. 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
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

expected output

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 When the children should also be deleted.

Delete child rows before the parent row
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 When child rows should always be deleted with their parent.

Add ON DELETE CASCADE to the FK definition
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

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 versions

ON 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 documentation ↗

MariaDB Server error code 1217 / ER_ROW_IS_REFERENCED

MariaDB foreign key ON DELETE actions ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors