Which option describes Update Set-to-Null?

Which option describes Update Set-to-Null?

• A. Updates to the parent key are blocked if there are references.
• B. On updating the primary key, referencing foreign keys are set to NULL.
• C. Referencing keys are updated to the new value.
• D. Set to the default value on update.

Answer: (B)

When a foreign key is defined with the action ON UPDATE SET NULL, updating the value of the parent key causes all referencing foreign keys in the child table to be set to NULL. This preserves referential integrity by dissociating the child rows from the old parent without cascading the new parent value. That’s why the statement describing updating the primary key and setting the referencing foreign keys to NULL matches Update Set-to-Null exactly. It also requires the foreign key columns to be nullable; otherwise setting them to NULL would not be allowed. Other actions illustrate different behaviors: blocking the update when references exist, cascading the update to propagate the new key value, or setting the foreign keys to a default value upon update.

When a foreign key is defined with the action ON UPDATE SET NULL, updating the value of the parent key causes all referencing foreign keys in the child table to be set to NULL. This preserves referential integrity by dissociating the child rows from the old parent without cascading the new parent value.

That’s why the statement describing updating the primary key and setting the referencing foreign keys to NULL matches Update Set-to-Null exactly. It also requires the foreign key columns to be nullable; otherwise setting them to NULL would not be allowed.

Other actions illustrate different behaviors: blocking the update when references exist, cascading the update to propagate the new key value, or setting the foreign keys to a default value upon update.