Referential Actions

The five referential actions determine what action a database takes when a reference is deleted or updated in a way that would affect other data. This section briefly examines each action, using the employees and departments tables above as an example.

Cascade

In a CASCADE action, everything that happens to the referenced key also happens to the referent. In our example, if the billing department were deleted, Betty Smith would also be deleted.

Restrict

The RESTRICT action tells the database to flag any update or delete operations as illegal if dependent rows exist. In this case, neither Betty Smith nor the billing department would be deleted, and the database would tell the user an illegal operation occurred.

No Action

NO ACTION tells the database to not perform any actions, in a similar manner to the RESTRICT action. However, if triggers are present, they may still be executed in a way that does not compromise the referential integrity of the database. Triggers are covered separately in this series of articles.

Set Null

The SET NULL action causes referent fields to be set to NULL if the underlying FOREIGN KEY is updated or deleted. This action requires the referent column to allow NULL values.

Set Default

The SET DEFAULT action operates in a similar manner to SET NULL, but instead of setting the value to NULL, the database sets the value to the column’s default value. An example of setting a default value is shown in Figure 3.