February 4, 2023 By Matthew Rathbone *

A “cascade” in the context of a database refers to the behavior of certain operations when they are performed on a parent record. In particular, when a parent record is deleted, any child records associated with it may also be deleted in a cascading manner. This can be useful in maintaining the integrity of the database, as it ensures that orphaned child records are not left behind when their parent is deleted.

👋 Check out our easy to use desktop GUI for SQL

Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!

Available for MacOS, Linux, and Windows.

Set with ON DELETE

In the case of Postgres, the “delete cascade” behavior is controlled by the “on delete” clause, which can be specified when a foreign key constraint is defined for a table. For example, if table A has a foreign key constraint that references table B, we can specify the “on delete cascade” behavior for that constraint like this:

ALTER TABLE A
ADD FOREIGN KEY (col_a) REFERENCES B (col_b)
ON DELETE CASCADE;

This will ensure that whenever a record in table B is deleted, any corresponding records in table A will also be deleted automatically. Note that this behavior only applies when the parent record is deleted; if a child record is deleted directly, the parent record will not be affected.

Use this power carefully

Cascading deletes make it easy to accidentally delete a whole lot of data by mistake when you only intended to delete a single record.

To avoid this situation, it is a good idea to use the “delete cascade” behavior only when it is strictly necessary. In many cases, it may be better to simply disallow the deletion of parent records if they have any associated child records, using a “restrict” or “no action” clause instead of a “cascade” clause.

DELETE CASCADE Summary

In summary, the “delete cascade” behavior in Postgres allows for the automatic deletion of child records when their parent is deleted. This can be useful for maintaining the integrity of the database, but it should be used carefully to avoid unintended consequences.