Foreign key ON DELETE behavior in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ON DELETE CASCADE option do in a foreign key constraint?

It automatically deletes all rows in the child table that reference the deleted row in the parent table. This keeps data consistent by removing dependent records.

beginner

Explain the ON DELETE SET NULL behavior in foreign keys.

When a referenced row in the parent table is deleted, the foreign key column in the child table is set to NULL. This means the child row no longer points to a parent row.

intermediate

What happens if a foreign key has ON DELETE RESTRICT and you try to delete a parent row?

The deletion is blocked if there are any child rows referencing the parent. You must first remove or update child rows before deleting the parent.

advanced

Describe the ON DELETE NO ACTION behavior.

It prevents deletion of a parent row if child rows exist, similar to RESTRICT. The difference is in timing: NO ACTION checks constraints at the end of the statement or transaction.

beginner

What is the default behavior if ON DELETE is not specified in a foreign key?

The default is usually NO ACTION, meaning the database will prevent deleting a parent row if child rows exist referencing it.

What does ON DELETE CASCADE do when a parent row is deleted?

A Deletes all child rows referencing the parent row

B Sets child foreign keys to NULL

C Prevents deletion of the parent row

D Does nothing automatically

ON DELETE CASCADE deletes all child rows that reference the deleted parent row to keep data consistent.

Which ON DELETE option sets the foreign key in child rows to NULL when the parent is deleted?

A CASCADE

B RESTRICT

C SET NULL

D NO ACTION

SET NULL sets the foreign key column in child rows to NULL when the referenced parent row is deleted.

If a foreign key uses ON DELETE RESTRICT, what happens when you try to delete a parent row with children?

A Deletion is blocked until children are removed

B Parent row is deleted and children updated

C Children are deleted automatically

D Foreign keys are set to NULL

RESTRICT blocks deletion of the parent row if child rows exist referencing it.

What is the main difference between NO ACTION and RESTRICT in ON DELETE behavior?

A NO ACTION deletes children, RESTRICT does not

B They behave exactly the same

C NO ACTION sets foreign keys to NULL, RESTRICT blocks deletion

D RESTRICT checks constraints immediately, NO ACTION checks at statement end

RESTRICT checks constraints immediately, while NO ACTION defers constraint checking until the end of the statement or transaction.

What is the default ON DELETE behavior if none is specified?

A SET NULL

B NO ACTION

C CASCADE

D RESTRICT

The default ON DELETE behavior is usually NO ACTION, which prevents deletion if child rows exist.

Describe the different ON DELETE behaviors available for foreign keys and when you might use each.

Think about how deleting a parent row affects related child rows.

Explain why ON DELETE CASCADE can be useful and what risks it might have.

Consider a real-life example like deleting a customer and their orders.