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

Need a 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

Need a 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

Need a 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

Need a hint?

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