Why does TRUNCATE TABLE fail on a table referenced by another table's foreign key?

## What the foreign key is promising `FOREIGN KEY (customer_id) REFERENCES customers(id)` is a standing invariant: for every row in `orders`, a matching `customers` row exists. The `ON DELETE` action tells the engine how to keep that invariant when a parent disappears — `CASCADE` deletes the children, `SET NULL` blanks the reference, `RESTRICT`/`NO ACTION` refuses the parent deletion while children remain. Every one of those actions is defined *per parent row*. The engine sees "this row is going", finds the children, and applies the rule. ## Why TRUNCATE cannot play `TRUNCATE` never enumerates parent rows. It discards the table's contents as one operation, which is precisely why it is fast. There is no per-row moment at which a cascade or a set-null could fire. That leaves an engine with three possible designs: silently break referential integrity, silently perform a cascade the user did not ask for, or refuse. The major engines refuse — a truncation of a table that another table's foreign key still references raises an error rather than producing orphans. ```sql -- orders.customer_id REFERENCES customers(id) TRUNCATE TABLE customers; -- error: still referenced by orders ``` Note that this refusal does not depend on whether `orders` currently has any rows. It is the *constraint* that blocks the statement, not the data, because the check is made against the schema rather than by scanning the child table. ## The ways through **Empty the child first.** Truncate `orders`, then `customers`. Order matters exactly as it does for deletes: children before parents. **Truncate them together.** Where the engine accepts a list of tables in one statement, naming every table in the referential closure satisfies it in a single shot: ```sql -- PostgreSQL: one statement, so no intermediate inconsistent state TRUNCATE TABLE orders, customers; ``` **Use CASCADE where offered.** PostgreSQL's `TRUNCATE TABLE customers CASCADE;` automatically truncates every table that references `customers`, transitively. This is far more destructive than the `ON DELETE CASCADE` you may be picturing: it does not delete *matching* children, it empties the entire referencing table, and then everything that references *that* table, and so on. Running it on a central table in a normalized schema can empty most of the database. Treat it as a scripted teardown tool, not a routine purge. **Use DELETE.** `DELETE FROM customers;` runs the declared referential actions properly. It is slower, but it is the statement whose semantics actually match "remove parents and handle the children as declared". ## Self-references and the empty-child case A table whose foreign key points at itself — `employees.manager_id REFERENCES employees(id)` — is usually truncatable, because every row including every parent goes at once, so no dangling reference can survive. Details vary by engine, but the intuition holds: the constraint can only be violated by rows that outlive the operation, and here none do. Equally, do not expect "the child table is currently empty" to help. Engines base the refusal on the existence of the constraint, so an empty `orders` table still blocks `TRUNCATE TABLE customers` on the engines that check the schema. ## Practical guidance For test-suite teardown and staging refreshes, keep an explicit, ordered truncation list — child tables first, parents last — rather than reaching for `CASCADE`. The list is self-documenting, it fails loudly when someone adds a new referencing table, and it never empties a table you did not intend to touch. Reserve `CASCADE` for throwaway environments where wiping the referential closure is exactly the goal. And when the requirement is genuinely "remove these parents and let the declared cascade rules deal with the children", that is `DELETE`. The refusal is the engine telling you that you asked the wrong statement to do a referential job.