Foreign key ON UPDATE behavior in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a foreign key in a database?

A foreign key is a column or set of columns in one table that refers to the primary key in another table. It creates a link between the two tables to maintain data consistency.

beginner

What does the ON UPDATE clause do in a foreign key constraint?

The ON UPDATE clause defines what happens to the foreign key values when the referenced primary key is updated. It controls how changes in the parent table affect the child table.

intermediate

Explain the ON UPDATE CASCADE behavior.

ON UPDATE CASCADE means that when the primary key in the parent table is updated, the foreign key values in the child table are automatically updated to match the new value.

intermediate

What happens with ON UPDATE SET NULL?

With ON UPDATE SET NULL, if the primary key in the parent table is updated, the foreign key values in the child table are set to NULL, indicating no reference.

advanced

Describe the difference between ON UPDATE RESTRICT and ON UPDATE NO ACTION.

ON UPDATE RESTRICT prevents the update of the primary key if there are matching foreign keys in the child table. ON UPDATE NO ACTION also prevents the update but checks the constraint at the end of the statement, which can allow deferred checks.

What does ON UPDATE CASCADE do in a foreign key constraint?

A Sets foreign keys to NULL when parent key changes

B Deletes rows in child table when parent key changes

C Updates foreign keys in child table when parent key changes

D Prevents updates to parent key if child rows exist

ON UPDATE CASCADE automatically updates the foreign key values in the child table to match the new primary key value in the parent table.

If a foreign key has ON UPDATE SET NULL, what happens when the parent key is updated?

A Foreign key values are deleted

B Foreign key values are set to NULL

C Update is blocked

D Foreign key values are unchanged

ON UPDATE SET NULL sets the foreign key values to NULL when the referenced primary key is updated.

Which ON UPDATE option prevents the parent key from being updated if child rows exist?

A CASCADE

B SET NULL

C SET DEFAULT

D RESTRICT

ON UPDATE RESTRICT blocks the update of the parent key if there are matching foreign keys in the child table.

What is the difference between ON UPDATE NO ACTION and ON UPDATE RESTRICT?

A NO ACTION checks constraints at statement end, RESTRICT checks immediately

B NO ACTION allows updates, RESTRICT blocks them

C NO ACTION deletes child rows, RESTRICT sets them NULL

D They behave exactly the same

ON UPDATE NO ACTION defers constraint checking until the end of the statement, while RESTRICT checks immediately and blocks the update if violated.

Which ON UPDATE behavior automatically updates child rows to match changes in the parent key?

A CASCADE

B SET NULL

C RESTRICT

D NO ACTION

CASCADE updates child rows automatically when the parent key changes.

Explain the different ON UPDATE behaviors available for foreign keys and when you might use each.

Think about how changes in the parent table should affect related child rows.

Describe a real-life example where ON UPDATE CASCADE would be helpful.

Imagine changing an ID that is used in many places.

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