PostgreSQL Cascade Delete: A Comprehensive Guide

Cascade delete is an important concept in PostgreSQL that helps maintain data integrity across related tables. When you delete records in a parent table, you normally want the references to those records in all child tables to be deleted as well.

In this comprehensive guide, we will cover the following topics:

• What is a cascade delete
• How cascade delete works
• Setting up cascade delete with foreign keys
• Cascade delete in action
• When not to use cascade delete
• Alternative referential actions
• Cascading updates
• Summary

What is a Cascade Delete

A cascade delete specifies that when a record in the parent table is deleted, the corresponding records in the child table that depend on the parent record should also be deleted automatically.

For example, let‘s say we have an orders table and an order_items table. The order_items table references the orders table via a foreign key constraint. If we delete an order, we would also want the related order items for that order to be deleted automatically. This is where we would specify a cascade delete on the foreign key constraint.

The cascade delete helps ensure data integrity and prevents orphan records in the child table that reference missing parent records.

How Cascade Delete Works

When you specify a cascade delete on a foreign key constraint, PostgreSQL handles the details of deleting dependent records behind the scenes.

Here is a high-level overview of what happens on a cascade delete:

1. You execute a DELETE statement on the parent table
2. PostgreSQL checks for any foreign key constraints that reference the records being deleted
3. For each referenced foreign key with a cascade delete rule, PostgreSQL generates a separate DELETE statement to remove the dependent records
4. The original DELETE is executed along with the generated cascade DELETE statements
5. The parent records and all related child records are deleted

An important thing to note is that cascade delete happens transactionally in an all or nothing manner. If any of the cascade delete actions fail, the entire operation is rolled back.

Setting up Cascade Delete with Foreign Keys

To setup a cascade delete, you specify it as the ON DELETE rule when defining a foreign key constraint.

Here is an example:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date DATE
);

CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL,
  quantity INTEGER
);

This creates an orders table and an order_items child table with a foreign key reference back to the orders table.

We specified ON DELETE CASCADE on the foreign key constraint, so if a record in orders is deleted, the matching records in order_items will also be automatically deleted.

You can also add a cascade delete rule to an existing foreign key by using ALTER TABLE:

ALTER TABLE order_items 
  DROP CONSTRAINT order_items_order_id_fkey,
  ADD CONSTRAINT order_items_order_id_fkey 
    FOREIGN KEY (order_id) 
      REFERENCES orders(order_id)
        ON DELETE CASCADE;

This modifies an existing foreign key to add the cascade delete rule.

Cascade Delete in Action

Now that we understand how to setup cascade delete, let‘s see it in action with some sample data.

First insert some orders:

INSERT INTO orders (customer_id, order_date)
VALUES 
  (123, ‘2023-02-14‘),
  (456, ‘2023-02-15‘),
  (789, ‘2023-02-16‘);  

Next insert some related order line items:

INSERT INTO order_items (order_id, product_id, quantity)
VALUES
  (1, 10, 1),
  (1, 20, 2),
  (2, 20, 1),
  (3, 30, 3); 

We now have 3 orders and 4 related order line items.

Now we can delete an order and observe the cascade delete:

DELETE FROM orders WHERE order_id = 1;

This will delete order 1 and the 2 related order line items will also be automatically deleted due to the cascade delete rule!

When Not to Use Cascade Delete

While cascade delete is very useful in most cases, there are some situations where you may want to use an alternative referential action.

You typically would not want a cascade delete if:

• Child records need to exist independently from parent records
• Deleting the child records would lose important historical data
• Child table data is used in reporting/analysis separate from parent table

Some examples where cascade delete may be problematic:

• Invoices and invoice line items
• Blog posts and comments
• Bug tracker issues and related comments

For these cases, you likely want child records to persist even if parent records are deleted.

Alternative Referential Actions

Instead of ON DELETE CASCADE, PostgreSQL provides these alternative referential actions for foreign keys:

• NO ACTION (default if not explicitly specified) – Prevents deletion of parent record
• RESTRICT – Same as NO ACTION
• SET NULL – Sets the foreign keys values to null instead of deleting records
• SET DEFAULT – Sets the foreign keys values to a default value

Some common uses cases:

• Set foreign keys to null when parent records are deleted
• Default child records to a particular user or status when parents deleted

For example:

-- Set order line item order_id to null when parent order deleted
CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(order_id) ON DELETE SET NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER  
);

-- Default order line items to assigned status 
CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,  
  order_id INTEGER REFERENCES orders(order_id) ON DELETE SET DEFAULT,
  status VARCHAR(20) DEFAULT ‘UNASSIGNED‘, 
  product_id INTEGER NOT NULL,
  quantity INTEGER
);

Cascading Updates

Similar to cascade delete, PostgreSQL also supports cascading updates. This allows updates to a parent table record to propagate to related child table records.

You define this using ON UPDATE CASCADE:

CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(order_id) ON UPDATE CASCADE ON DELETE CASCADE,
  product_id INTEGER NOT NULL,
  quantity INTEGER  
);

Now, if the order_id is updated in the orders table, it will automatically be updated to the new value for matching records in the order_items table.

Summary

To recap, key points about PostgreSQL cascade delete:

• Automatically deletes dependent child records when parent records deleted
• Helps preserve data integrity across related tables
• Set via ON DELETE CASCADE when defining foreign keys
• Cascades happen transactionally when deleting parent records
• Useful in most cases, but not all – consider business rules
• Alternative referential actions like SET NULL available
• Similar behavior for updates via ON UPDATE CASCADE

Taking the time to understand and effectively apply cascade delete rules in PostgreSQL is crucial for managing relational data consistency. Use this guide for reference when leveraging cascade capabilities within your projects.