Foreign key ON DELETE behavior in SQL - Mini Project: Build & Apply

Understanding Foreign Key ON DELETE Behavior in SQL

📖 Scenario: You are managing a simple database for a small library. The database has two tables: Authors and Books. Each book is linked to an author by a foreign key. You want to learn how deleting an author affects the books written by that author using different ON DELETE rules.

🎯 Goal: Build two tables, Authors and Books, with a foreign key from Books to Authors. Experiment with different ON DELETE behaviors to see how deleting an author affects the related books.

📋 What You'll Learn

Create an Authors table with columns AuthorID (primary key) and Name.

Create a Books table with columns BookID (primary key), Title, and AuthorID as a foreign key referencing Authors.AuthorID.

Add a foreign key constraint with ON DELETE behavior configurable in the project.

Insert sample data into both tables.

Write a query to select all books with their authors.

💡 Why This Matters

🌍 Real World

Managing related data in databases is common in many applications like libraries, stores, and social networks. Understanding foreign keys and ON DELETE behavior helps keep data consistent.

💼 Career

Database administrators and developers use foreign keys and ON DELETE rules to enforce data integrity and automate cleanup of related data.

1

Create the Authors and Books tables

Write SQL statements to create a table called Authors with columns AuthorID as an integer primary key and Name as text. Also create a table called Books with columns BookID as an integer primary key, Title as text, and AuthorID as an integer column (without foreign key constraint yet).

SQL

-- Create Authors table
-- Create Books table without foreign key

Hint

Use CREATE TABLE statements. Define primary keys with PRIMARY KEY. For now, do not add foreign key constraints.

2

Add the foreign key with ON DELETE behavior

Modify the Books table to add a foreign key constraint on AuthorID referencing Authors.AuthorID with ON DELETE CASCADE. This means if an author is deleted, their books will be deleted automatically.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);
-- Your code here

Hint

Add FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE inside the Books table definition.

3

Insert sample data into Authors and Books

Insert these exact rows into Authors: (1, 'Jane Austen'), (2, 'Mark Twain'). Insert these exact rows into Books: (1, 'Pride and Prejudice', 1), (2, 'Adventures of Huckleberry Finn', 2), (3, 'Emma', 1).

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);

-- Insert authors
-- Insert books

Hint

Use INSERT INTO with exact values given. Insert multiple rows in one statement.

4

Write a query to list all books with their authors

Write a SQL SELECT statement to get the Title of each book and the Name of its author by joining Books and Authors on AuthorID.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Jane Austen'), (2, 'Mark Twain');
INSERT INTO Books (BookID, Title, AuthorID) VALUES (1, 'Pride and Prejudice', 1), (2, 'Adventures of Huckleberry Finn', 2), (3, 'Emma', 1);

-- Write SELECT query to join Books and Authors

Hint

Use JOIN to combine Books and Authors on AuthorID. Select the book title and author name.

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