Foreign key ON UPDATE behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of ON UPDATE CASCADE on child table

Given two tables departments and employees where employees.dept_id is a foreign key referencing departments.id with ON UPDATE CASCADE, what will be the dept_id values in employees after updating departments.id from 10 to 20?

SQL

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), dept_id INT, 
  FOREIGN KEY (dept_id) REFERENCES departments(id) ON UPDATE CASCADE);

INSERT INTO departments VALUES (10, 'Sales'), (11, 'HR');
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 11);

UPDATE departments SET id = 20 WHERE id = 10;

SELECT id, name, dept_id FROM employees ORDER BY id;

A [{"id":1,"name":"Alice","dept_id":20},{"id":2,"name":"Bob","dept_id":20}]

B [{"id":1,"name":"Alice","dept_id":10},{"id":2,"name":"Bob","dept_id":11}]

C [{"id":1,"name":"Alice","dept_id":NULL},{"id":2,"name":"Bob","dept_id":11}]

D [{"id":1,"name":"Alice","dept_id":20},{"id":2,"name":"Bob","dept_id":11}]

💡 Hint

ON UPDATE CASCADE means changes to the parent key update the child keys automatically.

Because dept_id in employees has ON UPDATE CASCADE, when departments.id changes from 10 to 20, the corresponding dept_id in employees also updates from 10 to 20.

❓ query_result

intermediate

Result of ON UPDATE SET NULL on foreign key

Consider tables orders and customers where orders.customer_id references customers.id with ON UPDATE SET NULL. What will be the customer_id values in orders after updating customers.id from 5 to 50?

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE orders (id INT PRIMARY KEY, product VARCHAR(50), customer_id INT, 
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE SET NULL);

INSERT INTO customers VALUES (5, 'John'), (6, 'Jane');
INSERT INTO orders VALUES (101, 'Book', 5), (102, 'Pen', 6);

UPDATE customers SET id = 50 WHERE id = 5;

SELECT id, product, customer_id FROM orders ORDER BY id;

A [{"id":101,"product":"Book","customer_id":50},{"id":102,"product":"Pen","customer_id":6}]

B [{"id":101,"product":"Book","customer_id":null},{"id":102,"product":"Pen","customer_id":6}]

C [{"id":101,"product":"Book","customer_id":5},{"id":102,"product":"Pen","customer_id":6}]

D [{"id":101,"product":"Book","customer_id":6},{"id":102,"product":"Pen","customer_id":6}]

💡 Hint

ON UPDATE SET NULL sets the foreign key to NULL if the referenced key changes.

When customers.id changes from 5 to 50, the orders.customer_id referencing 5 is set to NULL because of ON UPDATE SET NULL.

📝 Syntax

advanced

Identify the syntax error in foreign key ON UPDATE clause

Which option contains a syntax error in defining a foreign key with an ON UPDATE action?

SQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE ???
);

A ON UPDATE CASCADE

B ON UPDATE SET NULL

C ON UPDATE DELETE

D ON UPDATE SET DEFAULT

💡 Hint

Check if the ON UPDATE action is a valid SQL keyword.

ON UPDATE DELETE is invalid syntax. The valid actions are CASCADE, SET NULL, SET DEFAULT, NO ACTION, and RESTRICT.

🧠 Conceptual

advanced

Understanding ON UPDATE RESTRICT behavior

What happens if you try to update a primary key value in a parent table that has a foreign key referencing it with ON UPDATE RESTRICT and there are matching rows in the child table?

A The update fails and raises an error preventing the change.

B The update succeeds but sets child foreign keys to NULL.

C The update succeeds and child rows update automatically.

D The update succeeds but deletes the child rows.

💡 Hint

RESTRICT prevents changes that break referential integrity.

ON UPDATE RESTRICT forbids updating the parent key if child rows reference it, so the update fails with an error.

❓ optimization

expert

Optimizing foreign key updates with ON UPDATE CASCADE

You have a large parent table categories and a child table items with a foreign key category_id referencing categories.id with ON UPDATE CASCADE. You need to update many categories.id values. Which approach is best to minimize performance impact?

A Update all <code>categories.id</code> values in a single transaction to trigger cascading updates once.

B Update each <code>categories.id</code> value in separate transactions to avoid locking.

C Disable foreign keys, update <code>categories.id</code>, then re-enable foreign keys.

D Delete and re-insert all rows in <code>items</code> after updating <code>categories.id</code>.

💡 Hint

Consider transaction overhead and cascading update behavior.

Updating all parent keys in one transaction triggers cascading updates efficiently and reduces locking overhead compared to multiple transactions or disabling constraints.

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