What are referential actions in SQL?

General Questions~4 min read

Referential actions define what a database does automatically to child rows when a parent row is deleted or updated in a foreign key relationship.

Theory

TL;DR

Think of a restaurant reservation system: delete a customer and the system either cancels their bookings (CASCADE), blocks the deletion until bookings are cleared (RESTRICT), or marks bookings as "no customer" (SET NULL)
Five types: CASCADE, RESTRICT, NO ACTION, SET NULL, SET DEFAULT
Use CASCADE for tightly coupled data (orders belong to a user); use RESTRICT or SET NULL when child rows can exist on their own (comments, logs)

Quick example

sql

CREATE TABLE users (
  id INT PRIMARY KEY
);

CREATE TABLE posts (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO users VALUES (1);
INSERT INTO posts VALUES (1, 1);

DELETE FROM users WHERE id = 1;
-- posts table is now empty; the row was removed automatically

CASCADE propagates the delete. Without it, the DELETE FROM users statement fails with a foreign key violation.

The five actions

CASCADE copies the operation down. Delete a parent, delete all children. Update a parent ID, update all references. Use it when child rows have no meaning without the parent.

RESTRICT blocks the operation if any child rows exist. You must remove children manually first. Good for protecting data from accidental loss.

NO ACTION behaves like RESTRICT in MySQL and PostgreSQL for standard usage. In PostgreSQL with deferred constraints, it allows temporary violations inside a transaction, deferring the check to commit. Use RESTRICT if you need consistent behavior across databases.

SET NULL writes NULL into the foreign key column when the parent is deleted. The child row stays, but the reference is cleared. This only works if the column allows NULL.

SET DEFAULT resets the foreign key to its default value. For example, orders from a deleted user fall back to user_id = 0 (a guest account). Uncommon, but useful for soft deletes and fallback ownership.

When to use

User deletes account, all their orders should go too: ON DELETE CASCADE
Comment stays after user is deleted, just unattributed: ON DELETE SET NULL on user_id
Prevent accidental deletion of users who still have orders: ON DELETE RESTRICT
Deleted product falls back to a default category: ON DELETE SET DEFAULT
Audit log must never lose its parent reference: ON DELETE NO ACTION (deferred in PostgreSQL)

How it works internally

On DELETE or UPDATE, the engine scans the child table's index on the foreign key column, finds matching rows, then applies the action: block, recurse, or write NULL/default. Row-level locks prevent race conditions. In PostgreSQL, deferred constraints move the check to transaction commit, letting you temporarily break integrity inside a transaction without immediate failure.

Common mistakes

Using SET NULL on a NOT NULL column. The transaction rolls back with a constraint violation. Always check that the column allows NULL before adding this action.

sql

-- Fails if user_id is declared NOT NULL
ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id)
  REFERENCES users(id) ON DELETE SET NULL;

Assuming CASCADE only touches direct children. I have seen this delete thousands of rows nobody intended to remove. If orders have order_items with their own CASCADE, deleting one user wipes all three tables in a single statement. Run a count query first: SELECT COUNT(*) FROM orders WHERE user_id = 1.

Forgetting ON UPDATE. The default for update operations is NO ACTION. Change a user ID while orders still reference the old one and the update fails. Add ON UPDATE CASCADE explicitly when parent IDs can change.

Treating NO ACTION and RESTRICT as identical across all databases. MySQL handles them the same way. PostgreSQL lets NO ACTION be deferred. Test in your target database.

Real-world usage

PostgreSQL / Supabase: CASCADE on auth.users -> profiles for account deletion flows
MySQL / WooCommerce: RESTRICT on orders.user_id to block deleting active customers
SQL Server / AdventureWorks: SET NULL on sales.customer_id for soft deletes
Oracle: NO ACTION on audit tables to block any parent modification

Follow-up questions

Q: What is the difference between RESTRICT and NO ACTION?
A: In MySQL and standard PostgreSQL they behave identically: immediate check on the statement. NO ACTION can be deferred in PostgreSQL, moving the check to transaction commit.

Q: Can CASCADE create infinite loops?
A: Yes, if two tables reference each other with CASCADE. PostgreSQL 12+ detects cycles and raises an error. Self-referencing tables like employees with a manager_id cascade through multiple levels but stop at the root.

Q: How do you disable foreign key checks during a migration?
A: MySQL: SET FOREIGN_KEY_CHECKS=0. PostgreSQL: SET session_replication_role = replica. Re-enable immediately after and validate data integrity.

Q: In a sharded database like CockroachDB, how do referential actions distribute?
A: Enforcement is local per shard. Cross-shard foreign keys require application-level logic or a gateway like Vitess. Distributed transactions add latency, which is worth mentioning at the senior level.

Examples

Basic: DELETE with CASCADE

sql

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2)
);

INSERT INTO users (name) VALUES ('Alice');           -- id = 1
INSERT INTO orders (user_id, total) VALUES (1, 99.99), (1, 49.99);

DELETE FROM users WHERE id = 1;
-- Both orders removed automatically
SELECT * FROM orders; -- returns 0 rows

Alice's account is gone. Both orders disappear in the same statement. No extra queries needed.

Intermediate: SET NULL for independent child rows

sql

CREATE TABLE authors (id SERIAL PRIMARY KEY, name VARCHAR(50));
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  author_id INT REFERENCES authors(id) ON DELETE SET NULL,
  title VARCHAR(100)
);

INSERT INTO authors (name) VALUES ('Bob');            -- id = 1
INSERT INTO articles (author_id, title) VALUES (1, 'SQL Deep Dive');

DELETE FROM authors WHERE id = 1;
-- Article stays, author_id becomes NULL
SELECT * FROM articles;
-- id=1, author_id=NULL, title='SQL Deep Dive'

The article survives. It loses its author reference, nothing else. This pattern fits content that has value after the creator is removed.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?