Using ON DELETE CASCADE and ON UPDATE RESTRICT

Imagine you’ve built a database with a customers table (customers) and their related orders (orders). But at some point, you face a question: what happens if a customer gets deleted from the customers table? Should their orders be deleted too, or should they just hang around as “orphans” pointing to a customer that doesn’t exist anymore? And what if you decide to change a customer’s ID? That’s where cascade operations (CASCADE) and constraints (RESTRICT) come in to control your database’s behavior.

ON DELETE CASCADE is a mechanism that automatically deletes related records when you delete a record from the parent table. In other words, if you delete a customer, all their orders get deleted too.

Here’s how it works. When you add ON DELETE CASCADE to a foreign key definition, the database “gets it” and will automatically delete the related record.

Example

Let’s say we have two tables: customers and orders. Customers (customers) can make several orders (orders), which is a ONE-TO-MANY relationship. We want all a customer’s orders to be deleted if the customer is deleted.

-- Create the customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Create the orders table with a foreign key referencing customers
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

Insert data into the tables

-- Insert data into customers
INSERT INTO customers (name) VALUES ('Ivan'), ('Anna');

-- Insert data into orders
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-01'),
(1, '2023-10-02'),
(2, '2023-10-03');

orders table:

Delete a customer and see what happens

-- Delete the customer with ID 1
DELETE FROM customers WHERE customer_id = 1;

-- Check what’s left in the orders table
SELECT * FROM orders;

As you can see, the orders related to the deleted customer were also deleted.

Restricting changes: ON UPDATE RESTRICT

ON UPDATE RESTRICT lets you prevent changes to a value in the parent table if a record in the child table references that value. It’s like a “safety barrier” that stops changes which could mess up your data integrity.

How does it work? When you add ON UPDATE RESTRICT, the database won’t let you update the key in the parent table if there are records in the child table referencing it.

Example

Let’s use the same customers and orders tables, but add an update restriction to the foreign key.

-- Recreate the orders table with an update restriction
DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Let’s try to update a customer’s ID

For this example, assume that orders already has at least one row whose customer_id = 2 (otherwise nothing references that key and the update would just succeed):

-- Make sure there's at least one order for customer 2
INSERT INTO orders (customer_id, order_date) VALUES (2, '2024-01-15');

-- Now try to change customer ID from 2 to 5
UPDATE customers
SET customer_id = 5
WHERE customer_id = 2;

Result:

ERROR:  update or delete on table "customers" violates foreign key constraint
DETAIL:  Key (customer_id)=(2) is still referenced from table "orders".

As you can see, the database threw an error because changing the key would break the relationship between the tables.

I’ll talk more about UPDATE and its quirks in the next level :P

Combining ON DELETE CASCADE and ON UPDATE RESTRICT

Of course, you can combine cascade operations (CASCADE) and restrictions (RESTRICT). For example, you can set up automatic deletion of related data when the parent record is deleted (ON DELETE CASCADE), but block changing its ID (ON UPDATE RESTRICT) to avoid unwanted side effects.

Example

Let’s create the orders table again, using both mechanisms:

DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Now:

• If you delete a customer, all their orders will be deleted.
• If you try to change a customer’s ID, you’ll get an error.

Why does this matter in real projects?

Using CASCADE and RESTRICT is super important for big systems with lots of related tables. For example:

In an online store, a customer can have orders. If a customer decides to delete their profile, you don’t want to leave orders in the database that don’t point to anything. That’s where ON DELETE CASCADE helps out.

At the same time, you might want to prevent accidental changes to unique keys so you don’t break relationships between tables. That’s what ON UPDATE RESTRICT is for.