Foreign key ON UPDATE behavior in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to specify the action when the referenced key is updated.

SQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE [1]
);

Drag options to blanks, or click blank then click option'

ACASCADE

BDELETE

CRESTRICT

DIGNORE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DELETE instead of CASCADE causes errors because DELETE is not valid for ON UPDATE.

Using IGNORE is not a valid SQL action for ON UPDATE.

The ON UPDATE CASCADE option means that when the referenced key in the parent table changes, the foreign key in the child table updates automatically to match.

2fill in blank

medium

Complete the code to prevent updates to the referenced key if child rows exist.

SQL

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE [1]
);

Drag options to blanks, or click blank then click option'

ACASCADE

BRESTRICT

CSET NULL

DNO ACTION

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing RESTRICT with CASCADE, which allows updates to propagate.

Using SET NULL here would set child keys to NULL, not restrict updates.

The ON UPDATE RESTRICT option prevents updating the referenced key if there are matching rows in the child table. It stops the update to keep data consistent.

3fill in blank

hard

Fix the error in the foreign key definition to correctly handle updates by setting child keys to NULL.

SQL

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON UPDATE [1]
);

Drag options to blanks, or click blank then click option'

ASET NULL

BNO DELETE

CRESTRICT

DCASCADE

Attempts:

3 left

💡 Hint

Common Mistakes

Using NO DELETE is invalid syntax and causes errors.

CASCADE updates child keys to new values instead of setting NULL.

The ON UPDATE SET NULL option sets the foreign key in the child table to NULL when the referenced key in the parent table is updated. This requires the foreign key column to allow NULL values.

4fill in blank

hard

Fill both blanks to create a foreign key that updates child keys on parent update and deletes child rows on parent delete.

SQL

CREATE TABLE comments (
  comment_id INT PRIMARY KEY,
  post_id INT,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON UPDATE [1] ON DELETE [2]
);

Drag options to blanks, or click blank then click option'

ACASCADE

BSET NULL

CRESTRICT

DNO ACTION

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing CASCADE with RESTRICT can cause unexpected errors.

Using SET NULL on delete requires child keys to allow NULL.

Using ON UPDATE CASCADE updates child keys when the parent key changes. Using ON DELETE CASCADE deletes child rows when the parent row is deleted. This keeps data consistent automatically.

5fill in blank

hard

Fill all three blanks to define a foreign key that restricts updates, sets child keys to NULL on delete, and uses NO ACTION on update.

SQL

CREATE TABLE subscriptions (
  subscription_id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE [1] ON DELETE [2] ON UPDATE [3]
);

Drag options to blanks, or click blank then click option'

ARESTRICT

BSET NULL

CNO ACTION

DCASCADE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CASCADE in all places can cause unwanted deletions or updates.

Confusing NO ACTION with RESTRICT; they behave differently in timing.

The foreign key uses ON UPDATE RESTRICT to block updates if child rows exist, ON DELETE SET NULL to set child keys to NULL when parent rows are deleted, and ON UPDATE NO ACTION to defer update checks until the end of the transaction.

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