Referential integrity enforcement in SQL - Mini Project: Build & Apply

Referential Integrity Enforcement in SQL

📖 Scenario: You are managing a small library database. You need to keep track of books and their authors. Each book must be linked to an author to ensure data consistency.

🎯 Goal: Create two tables, Authors and Books, and enforce referential integrity so that every book references a valid author.

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

Add a foreign key constraint on Books.AuthorID referencing Authors.AuthorID.

Ensure that deleting an author will delete all their books automatically.

💡 Why This Matters

🌍 Real World

Libraries, bookstores, and many applications use referential integrity to keep related data accurate and consistent.

💼 Career

Database administrators and developers must enforce referential integrity to prevent orphan records and maintain data quality.

1

Create the Authors table

Write a SQL statement to create a table called Authors with two columns: AuthorID as an integer primary key, and Name as a text field.

SQL

-- Your code here to create the Authors table

Need a hint?

Use CREATE TABLE with PRIMARY KEY on AuthorID.

2

Create the Books table

Write a SQL statement to create a table called Books with three columns: BookID as an integer primary key, Title as a text field, and AuthorID as an integer.

SQL

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name TEXT
);
-- Your code here to create the Books table

Need a hint?

Define BookID as primary key and include AuthorID as an integer column.

3

Add foreign key constraint

Modify the Books table creation to add a foreign key constraint on AuthorID that references Authors.AuthorID.

SQL

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

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    AuthorID INT,
    -- Your code here to add the foreign key constraint
);

Need a hint?

Use FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) inside the Books table definition.

4

Enforce cascading delete

Update the foreign key constraint on Books.AuthorID to include ON DELETE CASCADE so that deleting an author removes their books automatically.

SQL

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

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) -- Add cascading delete here
);

Need a hint?

Add ON DELETE CASCADE after the foreign key reference.