CASCADE delete preview in SQL - Mini Project: Build & Apply

CASCADE Delete Preview

📖 Scenario: You are managing a small library database. It has two tables: Authors and Books. Each book is linked to an author. When an author is deleted, all their books should also be deleted automatically.

🎯 Goal: Create the tables with a foreign key that uses ON DELETE CASCADE. Then delete an author and see that their books are also removed.

📋 What You'll Learn

Create a table called Authors with columns AuthorID (primary key) and Name (text).

Create a table called Books with columns BookID (primary key), Title (text), and AuthorID (foreign key referencing Authors.AuthorID with ON DELETE CASCADE).

Insert exactly two authors with AuthorID 1 and 2, and their names 'Alice' and 'Bob'.

Insert exactly three books: two books by author 1 and one book by author 2.

Delete the author with AuthorID 1 and observe that their books are also deleted.

💡 Why This Matters

🌍 Real World

Many applications use foreign keys with cascading deletes to keep related data consistent automatically, such as deleting a customer and all their orders.

💼 Career

Understanding cascading deletes is important for database design and maintenance roles, ensuring data integrity and simplifying cleanup tasks.

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 foreign key referencing Authors.AuthorID with ON DELETE CASCADE.

SQL

-- Create Authors table
-- Create Books table with ON DELETE CASCADE foreign key

Need a hint?

Use CREATE TABLE statements. For the foreign key in Books, add ON DELETE CASCADE to automatically delete books when their author is deleted.

2

Insert authors into Authors table

Write SQL INSERT statements to add two authors into the Authors table: one with AuthorID 1 and Name 'Alice', and another with AuthorID 2 and Name 'Bob'.

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 two authors below

Need a hint?

Use two INSERT INTO Authors statements with the exact AuthorID and Name values.

3

Insert books into Books table

Write SQL INSERT statements to add three books into the Books table: two books with BookID 1 and 2, titles 'Book A' and 'Book B' by author 1, and one book with BookID 3, title 'Book C' by author 2.

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, 'Alice');
INSERT INTO Authors (AuthorID, Name) VALUES (2, 'Bob');
-- Insert three books below

Need a hint?

Use three INSERT INTO Books statements with the exact BookID, Title, and AuthorID values.

4

Delete an author and observe cascade delete

Write an SQL DELETE statement to remove the author with AuthorID 1 from the Authors table. This should automatically delete all books by that author because of the ON DELETE CASCADE foreign key.

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, 'Alice');
INSERT INTO Authors (AuthorID, Name) VALUES (2, 'Bob');

INSERT INTO Books (BookID, Title, AuthorID) VALUES (1, 'Book A', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (2, 'Book B', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (3, 'Book C', 2);
-- Delete author with AuthorID 1 below

Need a hint?

Use a DELETE FROM Authors WHERE AuthorID = 1 statement to remove the author with ID 1.