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

Understanding Foreign Key ON UPDATE Behavior in SQL

📖 Scenario: You are managing a small library database. There are two tables: authors and books. Each book has an author, and the books table uses a foreign key to link to the authors table.Sometimes, author IDs need to be updated, and you want to see how the foreign key ON UPDATE behavior works to keep the database consistent.

🎯 Goal: Create two tables, authors and books, with a foreign key from books.author_id to authors.id. Set the foreign key to ON UPDATE CASCADE so that when an author's ID changes, the change cascades to the books table.

📋 What You'll Learn

Create an authors table with columns id (primary key) and name.

Create a books table with columns id (primary key), title, and author_id.

Add a foreign key constraint on books.author_id referencing authors.id with ON UPDATE CASCADE.

Insert sample data into both tables.

Update an author's id and observe the cascading effect on books.author_id.

💡 Why This Matters

🌍 Real World

Foreign keys with ON UPDATE behavior help keep related data consistent when primary keys change, which is common in real-world databases like customer or product IDs.

💼 Career

Understanding foreign key constraints and cascading updates is essential for database administrators and developers to maintain data integrity and avoid orphaned records.

1

Create the authors table

Write SQL code to create a table called authors with two columns: id as an integer primary key, and name as a text field.

SQL

-- Create the authors table with id and name columns
-- Your code here

Need a hint?

Use CREATE TABLE authors and define id as INTEGER PRIMARY KEY.

2

Create the books table with foreign key

Write SQL code to create a table called books with columns: id as integer primary key, title as text, and author_id as integer. Add a foreign key constraint on author_id referencing authors.id with ON UPDATE CASCADE.

SQL

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT
);
-- Create the books table with id, title, author_id and foreign key ON UPDATE CASCADE
-- Your code here

Need a hint?

Remember to add FOREIGN KEY (author_id) REFERENCES authors(id) ON UPDATE CASCADE inside the books table definition.

3

Insert sample data into authors and books

Write SQL insert statements to add these rows: into authors insert (1, 'Jane Austen') and (2, 'Mark Twain'); into books insert (1, 'Pride and Prejudice', 1) and (2, 'Adventures of Huckleberry Finn', 2).

SQL

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON UPDATE CASCADE
);
-- Insert sample data into authors and books
-- Your code here

Need a hint?

Use INSERT INTO statements with exact values for both tables.

4

Update an author's id and observe cascading

Write an SQL update statement to change the id of the author with id = 1 to 10. This should automatically update the author_id in the books table because of ON UPDATE CASCADE.

SQL

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON UPDATE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Jane Austen');
INSERT INTO authors (id, name) VALUES (2, 'Mark Twain');

INSERT INTO books (id, title, author_id) VALUES (1, 'Pride and Prejudice', 1);
INSERT INTO books (id, title, author_id) VALUES (2, 'Adventures of Huckleberry Finn', 2);
-- Update author id from 1 to 10
-- Your code here

Need a hint?

Use UPDATE authors SET id = 10 WHERE id = 1; to change the author ID.