Foreign key ON DELETE behavior in SQL - Deep Dive

Overview - Foreign key ON DELETE behavior

What is it?

A foreign key is a rule that links one table to another in a database. The ON DELETE behavior tells the database what to do with linked data when the original data is deleted. It controls if related data should be removed, kept, or changed automatically. This helps keep data accurate and connected.

Why it matters

Without ON DELETE behavior, deleting data could leave broken links or orphaned records, causing confusion and errors. It ensures that when you remove something important, the related data stays consistent or is cleaned up properly. This saves time and prevents mistakes in managing data.

Where it fits

Before learning this, you should understand basic database tables and foreign keys. After this, you can learn about advanced data integrity, cascading updates, and transaction management to handle complex data changes safely.

Mental Model

Core Idea

ON DELETE behavior defines how related data reacts automatically when the original linked data is deleted.

Think of it like...

Imagine a family photo album where each photo has a label linking it to a family member. If the family member moves away (deleted), ON DELETE behavior decides if the photo is removed, kept, or changed to a placeholder.

┌─────────────┐       ┌─────────────┐
│ Parent Table│       │ Child Table │
│ (Primary Key)│◄─────│ (Foreign Key)│
└─────────────┘       └─────────────┘
        │                     │
        │ DELETE              │ ON DELETE Behavior
        ▼                     ▼
  ┌─────────────┐       ┌─────────────────────┐
  │ Delete row  │       │ Action on child rows │
  │ in parent   │       │ (CASCADE, SET NULL,  │
  │ table       │       │ SET DEFAULT, NO ACTION,│
  └─────────────┘       │ RESTRICT)            │
                        └─────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Foreign Keys Basics

Concept: Learn what a foreign key is and how it links tables.

A foreign key is a column in one table that points to a primary key in another table. This creates a connection between the two tables. For example, an Orders table might have a CustomerID that links to the Customers table's ID. This ensures orders belong to valid customers.

Result

You understand that foreign keys create relationships between tables to keep data connected.

Knowing foreign keys is essential because they form the backbone of relational data integrity.

2

Foundation

What Happens When Data is Deleted?

Concept: Explore the problem of deleting data that other data depends on.

If you delete a customer from the Customers table, what happens to their orders in the Orders table? Without rules, those orders would point to a customer that no longer exists, causing confusion and errors.

Result

You see why deleting linked data without rules can break the database's consistency.

Understanding this problem motivates the need for ON DELETE behavior to manage linked data safely.

3

Intermediate

ON DELETE CASCADE Behavior

🤔Before reading on: do you think deleting a parent row should automatically delete child rows? Commit to yes or no.

Concept: Learn how CASCADE automatically deletes related rows when the parent is deleted.

ON DELETE CASCADE means if you delete a row in the parent table, all related rows in the child table are also deleted automatically. For example, deleting a customer removes all their orders too.

Result

Deleting a parent row cleans up all linked child rows, preventing orphaned data.

Understanding CASCADE helps automate cleanup and maintain data integrity without manual effort.

4

Intermediate

ON DELETE SET NULL and SET DEFAULT

🤔Before reading on: what do you think happens to child rows if the parent is deleted and ON DELETE SET NULL is set? Commit to your answer.

Concept: Learn how SET NULL and SET DEFAULT change child rows when the parent is deleted.

ON DELETE SET NULL sets the foreign key in child rows to NULL when the parent is deleted, meaning the link is removed but the child row stays. ON DELETE SET DEFAULT sets the foreign key to a default value instead. This keeps child rows but changes their link.

Result

Child rows remain but their foreign key values are updated to NULL or a default.

Knowing these options allows flexible handling of child data without deleting it.

5

Intermediate

ON DELETE NO ACTION and RESTRICT Differences

🤔Before reading on: do you think NO ACTION and RESTRICT behave the same or differently? Commit to your answer.

Concept: Understand the subtle difference between NO ACTION and RESTRICT behaviors.

ON DELETE RESTRICT prevents deleting a parent row if child rows exist, stopping the delete immediately. ON DELETE NO ACTION also prevents deletion but checks constraints at the end of the statement or transaction, allowing some deferred checks.

Result

Deletion is blocked if child rows exist, but timing of the check differs.

Recognizing this difference is important for managing transactions and avoiding unexpected errors.

6

Advanced

Combining ON DELETE with Transactions

🤔Before reading on: do you think ON DELETE behaviors apply immediately or can be deferred in transactions? Commit to your answer.

Concept: Learn how ON DELETE actions interact with database transactions and deferred constraints.

Some databases allow deferring foreign key checks until a transaction ends. This means ON DELETE actions like NO ACTION can be delayed, allowing complex changes before constraints are enforced. This helps in batch updates or complex deletes.

Result

ON DELETE behavior can be immediate or deferred, affecting how and when errors occur.

Understanding transaction interaction prevents surprises in multi-step data changes.

7

Expert

Performance and Locking Implications of ON DELETE

🤔Before reading on: do you think ON DELETE CASCADE always improves performance? Commit to your answer.

Concept: Explore how ON DELETE behaviors affect database performance and locking.

ON DELETE CASCADE can cause many rows to be deleted automatically, which may lock multiple tables and slow down operations. Large cascades can impact performance and cause deadlocks. Choosing the right ON DELETE behavior balances data integrity and system speed.

Result

ON DELETE actions influence database locking and performance, requiring careful design.

Knowing these effects helps design scalable databases and avoid costly slowdowns or deadlocks.

Under the Hood

When a DELETE command runs on a parent table, the database engine checks the foreign key constraints on child tables. Depending on the ON DELETE rule, it either deletes child rows, sets their foreign keys to NULL or default, blocks the delete, or defers the check. This is done using internal triggers or constraint checks to maintain data integrity automatically.

Why designed this way?

ON DELETE behaviors were created to automate maintaining relationships between tables without manual cleanup. Early databases required manual deletion of child rows, which was error-prone. These rules reduce human error and enforce consistent data states. Different behaviors exist to support various application needs and data models.

┌───────────────┐
│ DELETE parent │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check FK rules│
└──────┬────────┘
       │
       ├─────────────┐
       │             │
       ▼             ▼
┌───────────────┐ ┌───────────────┐
│ CASCADE:      │ │ SET NULL/DEF: │
│ Delete child  │ │ Update child  │
│ rows          │ │ foreign keys  │
└───────────────┘ └───────────────┘
       │             │
       ▼             ▼
┌───────────────┐ ┌───────────────┐
│ NO ACTION/    │ │ RESTRICT:     │
│ Check later   │ │ Block delete  │
└───────────────┘ └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ON DELETE CASCADE delete only the direct child rows or all related rows recursively? Commit to your answer.

Common Belief: ON DELETE CASCADE deletes only the immediate child rows directly linked to the deleted parent.

Reality: ON DELETE CASCADE deletes child rows recursively, meaning if those child rows have their own children with CASCADE, those are deleted too.

Why it matters: Not knowing this can cause unexpected large data loss when deleting a parent row.

Quick: Does ON DELETE SET NULL allow foreign key columns to be NOT NULL? Commit to yes or no.

Common Belief: ON DELETE SET NULL works even if the foreign key column is NOT NULL.

Reality: ON DELETE SET NULL requires the foreign key column to allow NULL values; otherwise, the delete will fail.

Why it matters: Trying to use SET NULL on NOT NULL columns causes errors and blocks deletes.

Quick: Is ON DELETE NO ACTION the same as RESTRICT in all databases? Commit to yes or no.

Common Belief: ON DELETE NO ACTION and RESTRICT are exactly the same and behave identically.

Reality: They differ in timing: RESTRICT checks immediately, NO ACTION can defer checks until transaction end in some databases.

Why it matters: Misunderstanding this can cause unexpected constraint violations or allow temporary inconsistent states.

Quick: Does ON DELETE behavior affect updates to foreign keys? Commit to yes or no.

Common Belief: ON DELETE behavior also controls what happens when foreign keys are updated in the parent table.

Reality: ON DELETE behavior only applies to deletions; ON UPDATE rules control updates separately.

Why it matters: Confusing these can lead to incorrect assumptions about data changes and integrity.

Expert Zone

1

Some databases allow deferring foreign key checks, enabling complex multi-step deletes and updates within transactions.

2

ON DELETE CASCADE can cause cascading locks and deadlocks in high-concurrency environments if not designed carefully.

3

Choosing SET DEFAULT requires careful default value selection to avoid invalid or meaningless foreign key references.

When NOT to use

Avoid ON DELETE CASCADE in systems where data deletion must be audited or reversible; instead, use soft deletes or manual cleanup. For strict data retention, use RESTRICT or NO ACTION. When foreign keys must never be null, avoid SET NULL. Use application logic or triggers for complex deletion rules.

Production Patterns

In production, ON DELETE CASCADE is common for child records tightly bound to parents, like order items to orders. SET NULL is used when child data can exist independently but loses its link. RESTRICT is used to prevent accidental deletes of important data. Deferred constraints are used in batch processing to improve performance.

Connections

Transaction Management

ON DELETE behaviors interact with transactions to control when constraints are checked and actions applied.

Understanding transactions helps predict when ON DELETE rules trigger errors or changes, enabling safer multi-step data operations.

Data Integrity

ON DELETE behavior is a key mechanism to enforce data integrity by maintaining consistent relationships between tables.

Knowing this connection clarifies why databases provide these rules and how they prevent data corruption.

Garbage Collection (Computer Science)

ON DELETE CASCADE is similar to garbage collection where unused linked data is automatically cleaned up.

Recognizing this parallel helps understand automatic cleanup mechanisms in databases and programming languages.

Common Pitfalls

#1 Deleting a parent row without considering ON DELETE behavior causes orphaned child rows.

Wrong approach:

DELETE FROM Customers WHERE CustomerID = 10;

Correct approach:

Ensure foreign key has ON DELETE CASCADE or manually delete child rows first:
DELETE FROM Orders WHERE CustomerID = 10;
DELETE FROM Customers WHERE CustomerID = 10;

Root cause:

Not understanding that child rows remain linked to a non-existent parent, breaking data integrity.

#2 Using ON DELETE SET NULL on a foreign key column that does not allow NULL values.

Wrong approach:

ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(ID) ON DELETE SET NULL;
-- But CustomerID is NOT NULL

Correct approach:

Alter the column to allow NULL before adding the constraint:
ALTER TABLE Orders ALTER COLUMN CustomerID DROP NOT NULL;
ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(ID) ON DELETE SET NULL;

Root cause:

Ignoring column nullability requirements for SET NULL behavior.

#3 Assuming ON DELETE NO ACTION and RESTRICT behave identically in all cases.

Wrong approach:

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(ID) ON DELETE NO ACTION
);

Correct approach:

Use RESTRICT if immediate constraint checking is required or understand NO ACTION defers checks:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(ID) ON DELETE RESTRICT
);

Root cause:

Misunderstanding timing differences in constraint enforcement.

Key Takeaways

Foreign key ON DELETE behavior controls how related data changes when the original data is deleted, ensuring database consistency.

Common ON DELETE options include CASCADE (delete related rows), SET NULL (remove link), SET DEFAULT (assign default), NO ACTION, and RESTRICT (block delete).

Choosing the right ON DELETE behavior depends on data relationships, application needs, and performance considerations.

Understanding how ON DELETE interacts with transactions and constraints prevents unexpected errors and data loss.

Misusing ON DELETE rules can cause orphaned data, errors, or performance issues, so careful design and testing are essential.

Practice

1. What does the ON DELETE CASCADE option do in a foreign key constraint?

easy

A. Deletes all related child rows automatically when the parent row is deleted.

B. Prevents deletion of the parent row if related child rows exist.

C. Sets the foreign key in child rows to NULL when the parent row is deleted.

D. Does nothing to child rows when the parent row is deleted.

Solution

1. Step 1: Understand ON DELETE CASCADE behavior

   This option means that when a parent row is deleted, all child rows linked by the foreign key are also deleted automatically.

2. Step 2: Compare with other options

   Other options like RESTRICT block deletion, and SET NULL clears the foreign key, so only CASCADE deletes child rows.

3. Final Answer:

   Deletes all related child rows automatically when the parent row is deleted. -> Option A

4. Quick Check:

   ON DELETE CASCADE = delete related rows [OK]

Hint: CASCADE means delete children when parent is deleted [OK]

Common Mistakes:

• Confusing CASCADE with RESTRICT
• Thinking CASCADE sets foreign keys to NULL
• Assuming CASCADE does nothing

2. Which of the following is the correct syntax to add a foreign key with ON DELETE SET NULL in SQL?

easy

A. FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE CASCADE

B. FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE SET NULL

C. FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE RESTRICT

D. FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE NO ACTION

Solution

1. Step 1: Identify the syntax for ON DELETE SET NULL

   The correct syntax includes the phrase ON DELETE SET NULL after the foreign key reference.

2. Step 2: Match options with syntax

   FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE SET NULL exactly matches the required syntax for setting foreign keys to NULL on parent deletion.

3. Final Answer:

   FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE SET NULL -> Option B

4. Quick Check:

   ON DELETE SET NULL syntax = FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE SET NULL [OK]

Hint: Look for 'ON DELETE SET NULL' phrase exactly [OK]

Common Mistakes:

• Choosing CASCADE instead of SET NULL
• Confusing RESTRICT and NO ACTION
• Missing ON DELETE clause

3. Given these tables:

CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE RESTRICT);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (10, 1);

What happens if you run DELETE FROM parent WHERE id = 1;?

medium

A. The parent row is deleted, and the child row is also deleted.

B. The parent row is deleted, but the child row's parent_id is set to NULL.

C. The delete succeeds and leaves the child row with a dangling parent_id.

D. The delete fails because the child row exists and ON DELETE RESTRICT blocks it.

Solution

1. Step 1: Understand ON DELETE RESTRICT effect

   RESTRICT prevents deleting a parent row if any child rows reference it.

2. Step 2: Apply to given data

   Since child row with parent_id=1 exists, deleting parent id=1 is blocked.

3. Final Answer:

   The delete fails because the child row exists and ON DELETE RESTRICT blocks it. -> Option D

4. Quick Check:

   ON DELETE RESTRICT blocks delete if children exist [OK]

Hint: RESTRICT blocks delete if child rows exist [OK]

Common Mistakes:

• Assuming CASCADE behavior with RESTRICT
• Thinking child foreign key becomes NULL
• Believing delete always succeeds

4. You wrote this SQL:

ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL;

But when you delete a parent row, the child row's parent_id does not become NULL. What is the likely problem?

medium

A. The foreign key syntax is incorrect and ignored by the database.

B. ON DELETE SET NULL only works with CASCADE.

C. The parent_id column is NOT NULL, so it cannot be set to NULL.

D. You must delete child rows manually before deleting parent.

Solution

1. Step 1: Check column nullability

   ON DELETE SET NULL requires the foreign key column to allow NULL values.

2. Step 2: Identify the cause

   If parent_id is NOT NULL, the database cannot set it to NULL, so it leaves it unchanged.

3. Final Answer:

   The parent_id column is NOT NULL, so it cannot be set to NULL. -> Option C

4. Quick Check:

   SET NULL needs nullable foreign key column [OK]

Hint: Foreign key must allow NULL for SET NULL to work [OK]

Common Mistakes:

• Assuming syntax error causes issue
• Thinking SET NULL requires CASCADE
• Believing manual delete is always needed

5. You have two tables:

CREATE TABLE orders (order_id INT PRIMARY KEY);
CREATE TABLE order_items (item_id INT PRIMARY KEY, order_id INT, FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE);

You want to delete an order and all its items safely. However, sometimes order_items.order_id can be NULL for items not linked to any order. What is the best ON DELETE behavior to use for the foreign key to avoid errors and keep data consistent?

hard

A. ON DELETE CASCADE, because it deletes all linked items automatically.

B. ON DELETE NO ACTION, to do nothing on delete.

C. ON DELETE RESTRICT, to prevent deleting orders with linked items.

D. ON DELETE SET NULL, so deleting an order sets linked items' order_id to NULL.

Solution

1. Step 1: Identify the goal

   You want to delete an order and automatically delete all its related items safely.

2. Step 2: Evaluate ON DELETE CASCADE

   CASCADE deletes all child rows (order_items) where order_id matches the deleted order, achieving the goal automatically.

3. Step 3: Handle NULL values

   Items with NULL order_id are not referencing any order, so CASCADE leaves them untouched, maintaining consistency without errors.

4. Final Answer:

   ON DELETE CASCADE, because it deletes all linked items automatically. -> Option A

5. Quick Check:

   Delete order + linked items safely = ON DELETE CASCADE [OK]

Hint: CASCADE deletes linked items automatically, ignores NULLs [OK]

Common Mistakes:

• Choosing SET NULL, which orphans items instead of deleting
• Using RESTRICT, blocking necessary deletes
• Picking NO ACTION, requiring manual item deletes