Why does deleting a parent row fail with a foreign-key error, and in what order must you delete?

## What the foreign key promises `FOREIGN KEY (customer_id) REFERENCES customers (id)` is a standing guarantee: for every row of `orders`, either `customer_id` is NULL or a `customers` row with that id exists. The database enforces the guarantee on **every** statement that could break it — inserts and updates on the child side, and deletes and key updates on the parent side. ## Why the parent delete is rejected When you run `DELETE FROM customers WHERE id = 7` and three orders still reference customer 7, completing the delete would leave those orders referencing a customer that does not exist. With the default referential action the engine refuses: you get a referential-integrity error, and because a SQL statement is atomic, **no** rows are removed — not the customer, not even the parts of the statement that would have been fine. Nothing is half-done, which is exactly the behaviour you want; the error is the constraint doing its job, not an obstacle to route around. ## Delete order is a topological order Draw the arrows the way the references point — child to parent — and delete against them, leaves first: ``` order_items ──> orders ──> customers ``` so the order is `order_items`, then `orders`, then `customers`. Generalised: a table may be deleted from only once nothing that still has rows references it. For a real schema with several children per parent and children of children, that is a topological sort of the reference graph. Practically, you find it by listing the foreign keys that point *at* each table you intend to empty and handling those tables first, recursively. Run the whole sequence inside one transaction: ```sql BEGIN; DELETE FROM order_items WHERE EXISTS (SELECT 1 FROM orders o WHERE o.id = order_items.order_id AND o.customer_id = 7); DELETE FROM orders WHERE customer_id = 7; DELETE FROM customers WHERE id = 7; COMMIT; ``` Note the ordering trap hidden in that script: the first statement must find the order ids *before* the orders are gone. If you delete the orders first, the child cleanup no longer knows which items to remove — and the parent delete would have failed anyway. Where the intermediate set is large or the correlation awkward, capture the ids first (into a temporary table or a list) and delete by that set. ## Self-referencing hierarchies A table can reference itself — `employees.manager_id REFERENCES employees(id)`, a category tree, a comment thread. The same rule applies within the one table: a row cannot go while another row still points at it. So you delete from the deepest level upward, or you first detach the children by setting their pointer to NULL (or reparenting them to the grandparent) and then delete. Collecting the subtree with a recursive query and deleting deepest-first is the usual approach; a single `DELETE ... WHERE id = :root` will simply fail while descendants exist. ## The two schema-level escape hatches **Cascading actions.** The foreign key can be declared with `ON DELETE CASCADE`, and then deleting the parent instructs the engine to remove the referencing children itself, recursively. That is a property of the constraint, declared in DDL when the table is created or altered — not something a `DELETE` statement can request at run time. It changes the blast radius of every future parent delete, which is precisely why it is a schema decision made deliberately rather than a convenience switched on to make an error go away. **Deferred checking.** SQL allows a constraint to be declared `DEFERRABLE` and then deferred within a transaction, so the check happens at `COMMIT` instead of at each statement. With deferral in force, order inside the transaction stops mattering — the parent may go first, provided the children are gone by commit time. Engine support for deferral genuinely differs, so treat it as something to verify in your database's documentation rather than assume. ## What interviewers listen for The candidate who says "just disable the constraints" has answered the wrong question. Turning enforcement off during a delete does not make the orphans acceptable; it makes them invisible until the constraint is re-enabled, and re-enabling it on a table full of orphans fails. The good answer is: read the reference graph, delete leaves first, wrap it in one transaction, and if this operation happens routinely, decide at the schema level whether the relationship deserves a cascading action.