RESTRICT, CASCADE and DROP TABLE CASCADE

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:

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.

Output contract for this stage

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

What you should see when done

$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.