Idriss Rami — Notes

ON DELETE CASCADE

3 min read

ON DELETE CASCADE is a foreign-key option in SQL that says: when a row in the parent table is deleted, also delete every row in the child table that pointed to it. It’s how a relational database keeps relationships consistent in the face of deletions, instead of leaving dangling references.

ALTER TABLE students
ADD FOREIGN KEY (major_code) REFERENCES majors(major_code)
ON DELETE CASCADE;

With this constraint in place, deleting a major from Majors automatically deletes every student in Students who pointed to that major. Cascading is convenient when the relationship is genuinely a parent-child one — when a student without a valid major shouldn’t exist at all. It’s dangerous if used carelessly: a single DELETE FROM Majors WHERE ... can erase a substantial fraction of the database.

Several alternatives govern what happens to the referencing row when its referent is deleted:

  • CASCADE — delete the referencing row as well (above).
  • RESTRICT — refuse to delete the referenced row if any referencing row exists. The database raises an error and the delete fails.
  • SET NULL — delete the referenced row and set the referencing row’s foreign-key column to NULL. Only works if the column allows NULL.
  • NO ACTION — similar to RESTRICT in most databases. The exact semantics are deferred slightly differently, but the practical effect is the same: a referenced row in use can’t be deleted.

The choice depends on the domain. For a Majors table where a student must have a valid major, RESTRICT is often safer — it forces the deletion to be deliberate, and the user has to explicitly clean up the dependent rows first. For a Comments table attached to a Post, CASCADE makes sense — delete the post, delete its comments.

The default when no ON DELETE clause is specified is NO ACTION in most engines (MySQL/InnoDB 8.0+, PostgreSQL, SQL Server, SQLite). In InnoDB, NO ACTION is equivalent to RESTRICT — both reject the parent delete immediately. The two differ only in standard SQL, where NO ACTION defers the check to the end of the statement while RESTRICT checks immediately.

CASCADE should not be treated as a default-safe choice. A single DELETE FROM users WHERE id = 5 against a deeply-cascaded schema can wipe out comments, attachments, sessions, audit logs, and anything else that referenced that user. The right rule of thumb: only cascade when the child rows are meaningless without the parent (a post’s comments, a user’s session tokens) and explicitly use RESTRICT or SET NULL everywhere else, so deletes that would lose meaningful data fail loudly.

ON UPDATE clauses with the same options govern what happens when the referenced primary key is updated rather than deleted. The same vocabulary, applied to changes rather than deletions.

This is a property of the Foreign key constraint, and one of the things to think about when designing a Relational schema.

Graph View

Backlinks