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

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

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

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

Hint

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

Practice

1. What does the ON UPDATE CASCADE option do in a foreign key constraint?

easy

A. Deletes child rows when the parent key changes.

B. Automatically updates child rows when the parent key changes.

C. Prevents any update on the parent key if child rows exist.

D. Sets child foreign key values to NULL when the parent key changes.

Solution

1. Step 1: Understand ON UPDATE CASCADE behavior

   ON UPDATE CASCADE means if the parent key changes, the child foreign keys update automatically to match.

2. Step 2: Compare options with definition

   Only Automatically updates child rows when the parent key changes. describes automatic update of child rows on parent key change.

3. Final Answer:

   Automatically updates child rows when the parent key changes. -> Option B

4. Quick Check:

   ON UPDATE CASCADE = automatic child update [OK]

Hint: CASCADE means child rows change with parent keys [OK]

Common Mistakes:

• Confusing CASCADE with DELETE behavior
• Thinking CASCADE prevents updates
• Mixing SET NULL with CASCADE

2. Which of the following is the correct syntax to add a foreign key with ON UPDATE SET NULL in SQL?

easy

A. FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE SET NULL

B. FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE CASCADE

C. FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE SET NULL

D. FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE RESTRICT

Solution

1. Step 1: Identify correct ON UPDATE syntax

   The syntax for foreign key with ON UPDATE SET NULL is: FOREIGN KEY (...) REFERENCES ... ON UPDATE SET NULL.

2. Step 2: Check options for ON UPDATE SET NULL

   FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE SET NULL matches the correct syntax exactly. Others either use ON DELETE or different actions.

3. Final Answer:

   FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE SET NULL -> Option A

4. Quick Check:

   ON UPDATE SET NULL syntax = FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE SET NULL [OK]

Hint: ON UPDATE SET NULL sets child keys to NULL on parent update [OK]

Common Mistakes:

• Using ON DELETE instead of ON UPDATE
• Mixing CASCADE and SET NULL in syntax
• Omitting REFERENCES keyword

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 UPDATE CASCADE
);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (10, 1);
UPDATE parent SET id = 2 WHERE id = 1;

What will be the value of parent_id in the child table after the update?

medium

A. 2

B. Update fails with error

C. NULL

D. 1

Solution

1. Step 1: Understand ON UPDATE CASCADE effect

   ON UPDATE CASCADE updates child foreign keys automatically when parent keys change.

2. Step 2: Apply update to parent and child

   Parent id changes from 1 to 2, so child.parent_id updates from 1 to 2 automatically.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   ON UPDATE CASCADE updates child keys = 2 [OK]

Hint: CASCADE updates child keys to new parent key value [OK]

Common Mistakes:

• Expecting child key to stay the same
• Thinking update causes error
• Assuming child key becomes NULL

4. You have this foreign key:

FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE RESTRICT

What happens if you try to update a parent id that is referenced by a child row?

medium

A. The child rows are deleted.

B. The update succeeds and child rows update automatically.

C. The child foreign keys are set to NULL.

D. The update is blocked and an error is raised.

Solution

1. Step 1: Understand ON UPDATE RESTRICT behavior

   ON UPDATE RESTRICT prevents updating parent keys if child rows reference them.

2. Step 2: Apply update attempt on referenced parent key

   Since child rows exist, update is blocked and error occurs.

3. Final Answer:

   The update is blocked and an error is raised. -> Option D

4. Quick Check:

   ON UPDATE RESTRICT blocks update if child exists [OK]

Hint: RESTRICT blocks parent key update if child exists [OK]

Common Mistakes:

• Confusing RESTRICT with CASCADE
• Expecting child keys to update automatically
• Thinking child keys become NULL

5. Consider 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 UPDATE SET NULL
);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (10, 1);
UPDATE parent SET id = 2 WHERE id = 1;

What will be the value of parent_id in the child table after the update, and why?

hard

A. 1, because updates to parent keys are restricted.

B. 2, because ON UPDATE CASCADE updates child keys.

C. NULL, because ON UPDATE SET NULL sets child keys to NULL on parent update.

D. Update fails with error due to foreign key constraint.

Solution

1. Step 1: Understand ON UPDATE SET NULL behavior

   ON UPDATE SET NULL sets child foreign keys to NULL when the parent key changes.

2. Step 2: Apply update to parent and child

   Parent id changes from 1 to 2, so child.parent_id is set to NULL automatically.

3. Final Answer:

   NULL, because ON UPDATE SET NULL sets child keys to NULL on parent update. -> Option C

4. Quick Check:

   ON UPDATE SET NULL sets child keys NULL on parent update [OK]

Hint: SET NULL sets child keys to NULL on parent key change [OK]

Common Mistakes:

• Confusing SET NULL with CASCADE
• Expecting child keys to update to new parent id
• Assuming update causes error