Foreign key ON UPDATE behavior in SQL - Step-by-Step Execution

Concept Flow - Foreign key ON UPDATE behavior

Start: Update parent key

↓

Check foreign key constraint

↓

ON UPDATE CASCADE

↓

Update child keys

↓

Commit changes

↓

End

When a parent key is updated, the database checks the foreign key rule and applies the ON UPDATE action to child rows accordingly.

Execution Sample

SQL

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
);

UPDATE parent SET id = 2 WHERE id = 1;

This code creates two tables with a foreign key and updates the parent key to show ON UPDATE CASCADE effect.

Execution Table

Step	Action	Parent.id Before	Parent.id After	Child.parent_id Before	Child.parent_id After	Notes
1	Initial state	1	1	1	1	Parent and child have id=1
2	Update parent.id from 1 to 2	1	2	1	1	Parent id updated, child not yet updated
3	Check foreign key ON UPDATE CASCADE	2	2	1	2	Child.parent_id updated to 2 to match parent
4	Commit changes	2	2	2	2	Update complete, foreign key constraint maintained
5	End	2	2	2	2	Execution finished

💡 Update completes successfully with child keys updated due to ON UPDATE CASCADE

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
parent.id	1	2	2	2
child.parent_id	1	1	2	2

Key Moments - 2 Insights

Why does child.parent_id change after updating parent.id?

Because the foreign key is defined with ON UPDATE CASCADE, the child rows automatically update their foreign key to match the new parent key, as shown in step 3 of the execution_table.

What would happen if ON UPDATE SET NULL was used instead of CASCADE?

The child.parent_id would be set to NULL after the parent.id update, instead of being updated to the new value. This behavior differs from the CASCADE action shown in step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of child.parent_id after step 2?

A 1

B 2

C NULL

D Undefined

💡 Hint

Check the 'Child.parent_id After' column in row for step 2.

At which step does the child.parent_id get updated to match the new parent.id?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for the step where 'Child.parent_id After' changes from 1 to 2.

If the foreign key used ON UPDATE SET NULL, what would child.parent_id be after the update?

A 1

B NULL

C 2

D Unchanged

💡 Hint

Refer to key_moments explanation about ON UPDATE SET NULL behavior.

Concept Snapshot

Foreign key ON UPDATE behavior:
- When a parent key changes, child keys react based on ON UPDATE rule.
- ON UPDATE CASCADE: child keys update to new parent key.
- ON UPDATE SET NULL: child keys become NULL.
- Ensures referential integrity automatically.
- Defined in FOREIGN KEY constraint syntax.

Full Transcript

This visual execution shows how foreign key ON UPDATE behavior works in SQL. When a parent table's key is updated, the database checks the foreign key constraint on the child table. If ON UPDATE CASCADE is set, child rows update their foreign keys to match the new parent key, maintaining integrity. The execution table traces each step: initial state, parent update, child update, and commit. Key moments clarify why child keys change and what would happen with different ON UPDATE actions. The quiz tests understanding of these steps and effects. This helps beginners see how foreign keys keep data consistent when parent keys change.

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