ON DELETE CASCADE deleted more rows than expected: how to see it coming

The short answer

ON DELETE CASCADE deletes every row that references the row you deleted, and every row that references those, in the same transaction.¹ Postgres’s answer, DELETE 1, counts only the rows the statement itself deleted.² So an AI agent, or a person, that trusts the command tag will report one row while dozens go. To see the real number, map the cascade chains from pg_constraint and count the delete inside a transaction you roll back.

This matters more once an agent holds the credentials. A person deleting a customer usually knows the orders go with it. An agent cleaning up “a duplicate record” knows what the statement says, and the statement doesn’t mention the other tables. In our challenge, deleting order 42 removes 4 rows, the order and its 3 line items, while the agent reports 1.⁷

The run: DELETE 1, 49 rows gone

A small store on PostgreSQL 18.6, on a throwaway cluster. Customers have orders, orders have line items, and refunds point at orders:

Code A database command in SQL. The text around it says what it does.

CREATE TABLE customers (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  status text NOT NULL);
CREATE TABLE order_items (
  id integer PRIMARY KEY,
  order_id integer NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku text NOT NULL);
CREATE TABLE refunds (
  id integer PRIMARY KEY,
  order_id integer REFERENCES orders (id) ON DELETE SET NULL,
  amount numeric NOT NULL);

Customer 7 has 12 orders, each order has 3 line items, and two of those orders have refunds. The 20 customers, 60 orders, 180 items and 4 refunds are generated; the full setup script is the one above plus four INSERT … SELECT generate_series lines.

Now the delete, inside a transaction so nothing is lost, followed by a look at what the transaction has done so far:

Code What the screen or file shows, copied as is.

BEGIN;
DELETE FROM customers WHERE id = 7;
DELETE 1
SELECT relname, n_tup_del AS deleted, n_tup_upd AS updated
FROM pg_stat_xact_user_tables
WHERE n_tup_del + n_tup_upd > 0
ORDER BY relname;
   relname   | deleted | updated
-------------+---------+---------
 customers   |       1 |       0
 order_items |      36 |       0
 orders      |      12 |       0
 refunds     |       0 |       2
(4 rows)

ROLLBACK;

DELETE 1. The transaction deleted 49 rows across three tables and updated two refunds, whose order_id became NULL. Run it for real and the counts afterwards agree: 19 customers, 48 orders, 144 line items, and 2 refunds that no longer point at any order.

RETURNING doesn’t help either. DELETE FROM customers WHERE id = 7 RETURNING id, name returns one row, customer 7, because RETURNING is computed over the rows the command itself deleted.²

Why the command tag says 1

The docs define the count in DELETE count as the number of rows deleted, and they describe it in terms of the statement’s own condition.² The cascaded rows are deleted by the foreign key’s action, not by your statement, and the run above shows they’re left out of the tag. The same is true of a SET NULL or SET DEFAULT action: those rows are updated, and the delete’s answer says nothing about updates.¹

That’s why the problem is quiet. Nothing fails. The statement has a WHERE, names one table and one id, and a reviewer reading the SQL would approve it. Any tool that relays the command tag reports one row, and the agent repeats it. It’s the same shape as the trigger that turned 1 row into 301: work the database does on its own, invisible in the answer.

Which tables will a delete reach?

Foreign keys live in pg_constraint. The column confdeltype holds the delete action: c cascade, n set null, d set default, r restrict, a no action. This query starts at one table and follows every cascade down, however deep, and stops at actions that don’t delete:

Code A database command in SQL. The text around it says what it does.

WITH RECURSIVE chain AS (
  SELECT c.conrelid AS child, c.confrelid AS parent, c.conname, c.confdeltype, 1 AS depth,
         ARRAY[c.confrelid::regclass::text, c.conrelid::regclass::text] AS path
  FROM pg_constraint c
  WHERE c.contype = 'f' AND c.confrelid = 'public.customers'::regclass
  UNION ALL
  SELECT c.conrelid, c.confrelid, c.conname, c.confdeltype, ch.depth + 1,
         ch.path || c.conrelid::regclass::text
  FROM pg_constraint c
  JOIN chain ch ON c.confrelid = ch.child
  WHERE c.contype = 'f' AND ch.confdeltype = 'c'
    AND NOT c.conrelid::regclass::text = ANY (ch.path)
)
SELECT depth, array_to_string(path, ' -> ') AS path, conname,
       CASE confdeltype WHEN 'c' THEN 'CASCADE (rows deleted)'
                        WHEN 'n' THEN 'SET NULL (rows updated)'
                        WHEN 'd' THEN 'SET DEFAULT (rows updated)'
                        WHEN 'r' THEN 'RESTRICT (delete refused)'
                        ELSE 'NO ACTION (delete refused at check time)' END AS on_delete
FROM chain ORDER BY depth, path;

On the store it prints:

Code What the screen or file shows, copied as is.

 depth |                path                |          conname          |        on_delete
-------+------------------------------------+---------------------------+-------------------------
     1 | customers -> orders                | orders_customer_id_fkey   | CASCADE (rows deleted)
     2 | customers -> orders -> order_items | order_items_order_id_fkey | CASCADE (rows deleted)
     2 | customers -> orders -> refunds     | refunds_order_id_fkey     | SET NULL (rows updated)

Run it for every table an agent can delete from, and keep the output next to the agent’s permissions. It is the list of tables the agent can change without ever naming them. Two things it doesn’t follow: ON UPDATE actions that a SET NULL might set off further down, and triggers, which can write anywhere.

TRUNCATE has its own version. TRUNCATE … CASCADE empties every table with a foreign key to the named tables, and the tables those pull in; the default, RESTRICT, refuses instead.⁴ The docs say plainly to be very careful with it.

Count a delete before it commits

The run above is the method. pg_stat_xact_user_tables counts the actions taken so far inside the current transaction, and the numbers update as the transaction goes.³ So:

1. BEGIN;
2. Run the delete.
3. Read pg_stat_xact_user_tables for every table with deletes or updates.
4. ROLLBACK; unless the counts are what you meant.

This counts what the cascades did, not what you hoped, because the database did the work. Three caveats. The counts are per table, not per row, so they tell you how much, not which rows. Triggers still fire during the trial run, so a trigger that calls out to another system (through dblink or an HTTP extension, say) has already acted. And the trial holds locks on every row it touched until you roll back, so on a busy table keep it short.

An agent can follow the same steps, but don’t rely on it to. A model that has already decided the row is a duplicate tends to read DELETE 1 as confirmation. The check belongs in whatever sits between the agent and the database.

Should you use RESTRICT instead?

Often, yes, for tables an agent can reach. With RESTRICT, deleting a customer who still has orders fails:

Code What the screen or file shows, copied as is.

DELETE FROM customers WHERE id = 3;
ERROR:  update or delete on table "customers" violates RESTRICT setting of foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(3) is referenced from table "orders".

The agent gets an error it has to explain, instead of a success it can misreport. Postgres’s default is NO ACTION, which also refuses a delete that would leave orphans, but checks it later, and can be deferred to the end of the transaction so other statements can fix things up first. RESTRICT can’t be deferred.¹

Cascades exist for good reasons: line items without an order are useless, and application code that deletes children by hand gets it wrong too. A workable split is to keep CASCADE where the children are pure parts of the parent (order to line items) and use RESTRICT where a delete would destroy history someone else relies on (customer to orders, order to refunds). Whatever you keep, also index the referencing columns: the docs point out that every delete scans the child table for matches, and a foreign key doesn’t create that index for you.¹

What about MySQL?

InnoDB has the same actions with two differences worth knowing. NO ACTION is the same as RESTRICT and refuses at once, and cascaded actions don’t fire triggers on the child tables.⁵ The bigger one is for auditing: MySQL’s 8.4 manual says InnoDB carries out cascading actions internally and logs none of their changes, so a tool that reads the binary log, Debezium included, doesn’t see the child rows.⁶ On MySQL you can still trial a delete inside a transaction with a SELECT COUNT(*) per child table before and after, but an audit built on the binary log won’t see the cascaded rows.

A checklist before an agent can delete

• Run the chain query for every table the agent’s role can delete from, and read every CASCADE, SET NULL and SET DEFAULT in the output.
• Switch to RESTRICT wherever a delete would destroy records other people rely on.
• Don’t take DELETE n or RETURNING as the size of a delete; both count only the named table.
• For anything bigger than one row, count inside a transaction with pg_stat_xact_user_tables, then commit or roll back.
• Don’t give the agent’s role TRUNCATE; if it must have it, never with CASCADE.
• Record what committed, not what was reported: the change log shows cascaded deletes in the same transaction, and an audit that reads it can see them. On MySQL, plan for the gap above.
• Keep a way back: a recent backup you’ve restored at least once, and for agent sessions, an undo built from the change log.

Questions people ask

Does DELETE RETURNING show rows deleted by a cascade?

No. RETURNING is computed over the rows the DELETE command itself deleted, so a cascade’s rows in other tables never appear.² In the run above it returned one row while 49 were deleted.

How do I find all ON DELETE CASCADE foreign keys in Postgres?

Query pg_constraint for rows with contype = 'f' and confdeltype = 'c'. To see full chains rather than single links, use the recursive query in this post, which follows each cascade down from one table.

Can I see how many rows a cascade will delete without deleting them?

Yes. Run the delete inside BEGIN, read pg_stat_xact_user_tables, which counts the current transaction’s deletes and updates per table, then ROLLBACK.³ Triggers still fire during the trial, so watch for ones with side effects.

What is the difference between RESTRICT and NO ACTION?

Both refuse a delete that would leave child rows behind. In Postgres, NO ACTION is the default and its check can be deferred to the end of the transaction; RESTRICT can’t be.¹ In MySQL’s InnoDB the two are the same.⁵

Try it

The cascade task in the challenge lets you run the delete against a copy of the store and compare the agent’s answer with what committed. In our open benchmark, a cascade that deletes 12 orders along with one customer is one of the cases Deedglass flags after commit, because its check reads the transaction’s rows from Postgres’s change log rather than the command tag.⁸ The benchmark post lists the cases it misses too. To see which of your own tables an agent could reach, npx deedglass scan grades its setup locally (what the grade means), and the playground shows a database doing more than one statement said.

Written with AI assistance and checked against our claims register.