Suggest an edit

Improve this article

Refine the answer for “What are referential actions in SQL?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

**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.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts1030 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.