SQL Foreign Key Cascades and Referential Actions Explained

What You’ll Learn

In this lesson, you will learn how foreign-key referential actions control what happens to related rows when a parent row is deleted or its key is updated. The examples use customers and orders so you can see how database design choices affect real application data.

• Use ON DELETE and ON UPDATE actions.
• Compare CASCADE, RESTRICT, SET NULL, and NO ACTION.
• Choose an action based on whether related records should be deleted, preserved, or rejected.
• Test how a customer and its orders behave when the customer key changes or the customer is deleted.

The Concept

A foreign key connects a child table to a parent table. For example, orders.customer_id can reference customers.customer_id. This prevents an order from referring to a customer that does not exist.

A referential action defines what the database should do when the referenced customer is updated or deleted. You configure these actions as part of the foreign-key constraint:

• CASCADE: Apply the parent change to related child rows. Deleting a customer deletes its orders; updating a customer key updates the matching foreign keys.
• RESTRICT: Reject the parent change immediately when related rows exist.
• SET NULL: Replace the child foreign key with NULL. The foreign-key column must allow null values.
• NO ACTION: Reject the change if it would leave an invalid reference. In PostgreSQL, this is the default and can be deferred when the constraint is declared deferrable.

These actions are not interchangeable. Cascading deletion is appropriate when child data has no meaning without its parent, such as order-line records belonging to an order. It is usually unsafe for information that must be retained for legal, financial, or reporting reasons.

The examples below use PostgreSQL syntax. Other database systems support similar actions, but details such as whether RESTRICT can be deferred may differ.

Basic Example

Here, an order belongs to a customer. The foreign key uses ON DELETE CASCADE and ON UPDATE CASCADE. Deleting a customer removes that customer’s orders, while changing the customer’s key updates the orders automatically.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_total NUMERIC(10, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

INSERT INTO customers (customer_id, customer_name)
VALUES
    (101, 'Amina Patel'),
    (102, 'Jon Bell');

INSERT INTO orders (order_id, customer_id, order_total)
VALUES
    (5001, 101, 89.50),
    (5002, 101, 42.00),
    (5003, 102, 125.75);

UPDATE customers
SET customer_id = 1001
WHERE customer_id = 101;

SELECT order_id, customer_id, order_total
FROM orders
ORDER BY order_id;

DELETE FROM customers
WHERE customer_id = 1001;

SELECT customer_id, customer_name
FROM customers
ORDER BY customer_id;

SELECT order_id, customer_id, order_total
FROM orders
ORDER BY order_id;

Expected Output

After the update, both orders originally belonging to customer 101 refer to 1001. After the delete, those two orders are removed automatically. The remaining customer and order are:

order_id | customer_id | order_total
---------+-------------+------------
5001     | 1001        | 89.50
5002     | 1001        | 42.00
5003     | 102         | 125.75

customer_id | customer_name
------------+--------------
102         | Jon Bell

order_id | customer_id | order_total
---------+-------------+------------
5003     | 102         | 125.75

How the Code Works

The orders_customer_fk constraint establishes the relationship between the two tables. Because orders.customer_id is also NOT NULL, every order must have a customer.

ON UPDATE CASCADE matters when the referenced key changes. In many systems, a primary key is an immutable surrogate identifier, so key updates are uncommon. However, the action still protects consistency if an import, migration, or administrative correction changes a customer ID.

ON DELETE CASCADE makes the order rows dependent on their customer. When customer 1001 is deleted, PostgreSQL automatically deletes orders 5001 and 5002. The application does not need a separate delete statement for each order.

Be careful with this design: one customer delete can remove many rows. For historical order systems, a safer choice may be RESTRICT, or the application may use a status such as inactive instead of physically deleting the customer.

Foreign-key cascades perform relationship maintenance, not general business auditing. If you need to record who deleted a customer and when, consider a separate audit design. For PostgreSQL-specific auditing of data changes, see PostgreSQL database triggers.

Another Example

Suppose a store must keep completed orders for reporting. In this design, deleting a customer is blocked while orders exist, but deleting an order removes its private order notes. The customer key can still be updated safely.

This combines different actions at different relationship levels:

• sales_orders.customer_id uses RESTRICT for customer deletion.
• sales_order_notes.order_id uses CASCADE because notes have no meaning without their order.
• sales_orders.customer_id uses ON UPDATE CASCADE so key changes remain consistent.

CREATE TABLE customer_accounts (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE sales_orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    CONSTRAINT sales_orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customer_accounts (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE sales_order_notes (
    note_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    note_text TEXT NOT NULL,
    CONSTRAINT sales_order_notes_order_fk
        FOREIGN KEY (order_id)
        REFERENCES sales_orders (order_id)
        ON DELETE CASCADE
);

INSERT INTO customer_accounts (customer_id, customer_name)
VALUES (201, 'Maya Chen');

INSERT INTO sales_orders (order_id, customer_id, order_status)
VALUES (7001, 201, 'completed');

INSERT INTO sales_order_notes (note_id, order_id, note_text)
VALUES (9001, 7001, 'Customer requested gift packaging.');

UPDATE customer_accounts
SET customer_id = 2201
WHERE customer_id = 201;

DELETE FROM sales_orders
WHERE order_id = 7001;

SELECT note_id, order_id, note_text
FROM sales_order_notes;

The customer key update changes sales_orders.customer_id from 201 to 2201. Deleting the order then removes its note through the second cascade. Attempting to delete customer 2201 while the order still exists would fail because of RESTRICT.

Use SET NULL when the child record should remain but its relationship is optional. For example, an order could have a nullable sales_representative_id. If that representative is deleted, setting the column to NULL preserves the order. Do not use SET NULL on a NOT NULL foreign-key column.

NO ACTION is often suitable when you want the default rejection behavior. In PostgreSQL, it differs from RESTRICT mainly because a deferrable NO ACTION constraint can check the relationship at transaction commit rather than immediately.

Common Mistakes

• Assuming every database has the same behavior. Referential-action support and timing details vary by database engine. Check the documentation for the database you deploy.
• Using CASCADE for valuable history. A customer delete can remove orders, payments, notes, or other dependent records. Use RESTRICT or soft deletion when history must remain.
• Choosing SET NULL for a required relationship. The foreign-key column must permit NULL, and the application must correctly handle an unassigned relationship.
• Confusing a foreign-key cascade with a trigger. A cascade maintains related rows. It does not automatically create an audit record, send a notification, or perform arbitrary application logic.
• Expecting an update cascade to fix unrelated references. It only affects foreign keys that reference the changed key through that constraint.

Try It Yourself

Create a PostgreSQL schema with customers and orders. Configure the relationship so that:

• Changing a customer ID updates matching orders.
• Deleting a customer is rejected while orders exist.
• Both tables contain at least two sample rows.

Test both behaviors with an UPDATE and a DELETE. The update should succeed, while the delete should fail until the related orders are removed or reassigned.

Challenge

Design a small customer-order schema with three tables:

• customers with a primary key and customer name.
• orders with a foreign key to customers.
• payments with a foreign key to orders.

Choose referential actions that satisfy all of these requirements:

• A customer cannot be deleted while orders exist.
• If an order is deleted, its payments are deleted automatically.
• If a customer ID changes, the orders continue to reference that customer.
• Insert sample data, update one customer ID, delete that customer’s order, and query the payments to verify the cascade.

Solution

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_total NUMERIC(10, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    CONSTRAINT payments_order_fk
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE
);

INSERT INTO customers (customer_id, customer_name)
VALUES
    (301, 'Rosa Garcia'),
    (302, 'Evan Smith');

INSERT INTO orders (order_id, customer_id, order_total)
VALUES
    (8001, 301, 64.00),
    (8002, 302, 118.25);

INSERT INTO payments (payment_id, order_id, amount)
VALUES
    (9501, 8001, 64.00),
    (9502, 8002, 118.25);

UPDATE customers
SET customer_id = 3301
WHERE customer_id = 301;

DELETE FROM orders
WHERE order_id = 8001;

SELECT payment_id, order_id, amount
FROM payments
ORDER BY payment_id;

The customer update succeeds because the order foreign key uses ON UPDATE CASCADE. The customer delete policy is RESTRICT, protecting order history. When order 8001 is deleted, payment 9501 is removed automatically through ON DELETE CASCADE; payment 9502 remains because order 8002 still exists.

Key Takeaways

• Foreign-key referential actions define how related rows respond to parent updates and deletes.
• CASCADE is useful for dependent data, but it can remove many rows unexpectedly.
• RESTRICT and NO ACTION protect parent records when related rows still exist.
• SET NULL preserves the child row but requires a nullable foreign-key column.
• Choose actions according to the business meaning and retention requirements of the data.