RESTRICT, CASCADE and DROP TABLE CASCADE

When a DELETE removes a parent row, search each referencing child for rows that name it: refuse with 01707 under RESTRICT, delete them in the same statement under ON DELETE CASCADE, level after level; refuse DROP TABLE and TRUNCATE of a referenced table with 01708 unless DROP TABLE ... CASCADE drops the foreign keys first.

Not run yet · 9 tests

Why this stage exists

The last stage stopped orphans at the door: no order can be inserted for a user who does not exist. But users can leave. Delete ada, and her orders name a user who is gone: the same orphans, created from the other side.

So a foreign key also says what a DELETE of a parent does to its children. There are two answers here:

RESTRICT, the default, or written ON DELETE RESTRICT: a parent that still has children cannot be deleted, 01707.
ON DELETE CASCADE: the children are deleted with it, and their own children with them.

Searching the children

For every parent row a DELETE removes, each child table that references it is searched for rows with that value. This is the foreign key's check run backwards, from parent to child, and it is where the index that was not created for you matters:

delete from users where id = 5: no index on orders (user_id)

ran

SeqScan (table=orders)

the same, after create index by_user on orders (user_id)

ran

IndexScan (table=orders, index=by_user, key=5)

rows_examined=201 without the index; rows_examined=3 index_lookups=2 with it.

A user with no orders is found to have none just as slowly. That is why nearly every database guide says to index foreign key columns, and why none of them does it for you: a child table that is never searched this way pays for the index on every insert.

Cascades are one statement

Under CASCADE, each deleted child is itself a parent row being deleted, so its own children are searched: users, then orders, then items. All of it is one statement. If any search on the way finds a RESTRICT child, the whole DELETE is 01707, and no user, no order and no item is deleted.

Each row is deleted before its children are searched, so a search sees the table as the statement has left it so far. A row that references only itself is therefore deleted under RESTRICT, since by the time its children are searched it is gone. Nothing needs planning ahead for all-or-nothing: a 01707 fails the statement, and a failed statement already undoes everything it changed.

An UPDATE cannot orphan by deleting, but it can by changing the value children name: an UPDATE that changes a unique target column some child row references is 01707, whatever the action. Updating any other column is fine.

Since a cascade is just pages changed by one statement, everything the transactions part built already covers it: ROLLBACK, a failed statement and crash recovery bring every level back, index entries included. Nothing new is needed, and a test checks it.

The success line counts the rows the DELETE found, not the cascaded ones: deleting one user with two orders prints (1 rows).

Dropping a parent table

Dropping users while orders references it would leave every order pointing nowhere, so DROP TABLE and TRUNCATE of a referenced table are 01708. DROP TABLE users CASCADE says you mean it: it first drops the foreign keys that reference users, then the table. The children keep their columns and rows, values and all; the column simply stops being a foreign key. A table's references to itself do not count, so a self-referencing table drops normally. The unique index a foreign key targets is a dependency too: DROP INDEX of it is 01708.

The algorithm in words

Deleting a parent row

Search, then refuse or cascade.

1.Find the DELETE's rows first, as before.
2.Delete each row, then, for each foreign key that references its table, search the child for rows whose foreign key value is this row's target value: through an index on that column if there is one, otherwise by reading the whole child.
3.If the foreign key is RESTRICT and the search found a row, the statement is 01707 and changes nothing.
4.If it is CASCADE, delete the rows found, with their index entries, and treat each as a parent row being deleted: search its own children the same way.
5.Only when every search has passed is the statement done; any 01707 undoes all of it, as a failed statement always is.
6.An UPDATE that changes a unique target value some child row references is 01707, whatever the action.

Dropping a parent table

Dependencies first.

1.DROP TABLE or TRUNCATE of a table referenced by another table's foreign key is 01708.
2.DROP INDEX of the unique index a foreign key targets is 01708.
3.DROP TABLE t CASCADE drops those foreign keys from their tables, keeping their columns and rows, then drops t.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 76.

Input	Output
`delete from users where id = 1;` with orders, RESTRICT	`[ERROR:01707] ...`
`delete from users where id = 5;` with CASCADE, 200 orders, no index	`Delete statement executed successfully (1 rows)`, `[EXEC] rows_examined=201`
the same with an index on `orders (user_id)`	`[EXEC] rows_examined=3 index_lookups=2`
`drop table users;` while orders references it	`[ERROR:01708] ...`
`drop index users_email_key;` while a foreign key targets `users (email)`	`[ERROR:01708] ...`
`update users set email = 'z@x' where id = 2;` while a note names user 2's email	`[ERROR:01707] ...`
`catalog list`	`... user_id INT REFERENCES users(id) ON DELETE CASCADE ...`
`ast drop table users cascade;`	`Statement: DROP TABLE`, `Table: users`, `Cascade: true`

What you should see when done

shell

$droid --db cascade.db
Welcome to droid!
>create table users (id int primary key, name varchar(20));
Create table statement executed successfully
>create table orders (id int primary key, user_id int references users (id) on delete cascade);
Create table statement executed successfully
>insert into users (id, name) values (1, 'ada');
Insert statement executed successfully (1 rows)
>insert into orders (id, user_id) values (10, 1);
Insert statement executed successfully (1 rows)
>insert into orders (id, user_id) values (11, 1);
Insert statement executed successfully (1 rows)
>delete from users where id = 1;
Delete statement executed successfully (1 rows)
[EXEC] rows_examined=3
>select count(*) from orders;
COUNT(*)
0
(1 rows)
[EXEC] rows_examined=0
>.exit
exiting.. good bye!

Common bugs in this stage

Leaving part of a cascade in place after a 01707. The failed statement must leave every level as it was.
Searching the children before deleting the row. A row that references only itself is then wrongly refused.
Cascading without the children's index entries. index check must still pass.
Counting cascaded rows in the success line. It counts the rows the DELETE found.
Leaving REFERENCES in the catalog after DROP TABLE ... CASCADE, or deleting the child rows.
Blocking DROP TABLE of a table that only references itself.

Language notes

The page above is the same for every language. These are the only language-specific hints.

C: recursion over (table, value) is natural; a deep chain of cascades is deep recursion, so keep each frame small.
C++: an explicit std::vector worklist of (table, key) pairs avoids deep recursion; it still deletes each row before searching its children.
Rust: collect a child search's results into a Vec before deleting them, so no tree is borrowed while it is changed.
Zig: an ArrayList worklist of (table, key) pairs keeps cascades off the call stack; free it on the error path too.

Loading starter code…

No run yet

9 tests wait for your first run

Run tests builds your code, drives the binary through every case for this stage, and shows expected and received output side by side here. Failing cases come first.