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.