ELEMENTS USED IN LOGICAL DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 34

fails unless all matching child entries are removed first. Table 2.1 describes
the various options that can be set when an action takes place on a parent
or child entity.

Table 2.1 Referential Integrity Options for a Relationship

Entity Action Available Actions

Parent entity INSERT None: Inserting a new instance has no effect on the child entity.

UPDATE None: This does not affect any records in the child entity, nor does it

prevent updates that result in mismatched data between the parent and
child entities.
Restrict: Checks data in the primary key value of the parent entity
against the foreign key value of the child entity. If the value does not
match, prevents the update from taking place.
Cascade: Duplicates changes in the primary key value of the parent
entity to the foreign key value in the child entity.
Null (Set Null): Similar to Restrict; if the value does not match, sets
the child foreign key value to NULL and permits the update.

DELETE None: This does not affect any records in the child entity; it may result

in orphaned instances in the child entity.
Restrict: Checks data in the primary key value of the parent entity
against the foreign key value of the child entity. If the value does not
match, prevents the delete from taking place.
Cascade: Deletes all matching entries from the child entity (in addition
to the instance in the parent entity) based on the match of primary key
value and foreign key value between the entities.
Null (Set Null): Similar to Restrict; if the value does not match, sets
the child foreign key value to NULL (or a specified default value) and
permits the delete. This creates orphaned instances in the child entity.

Child entity INSERT None: Takes no action; enforces no restrictions.

Restrict: Checks data in the primary key value of the parent entity
against the foreign key value being inserted into the child entity. If the
value does not have a match, prevents the insert from taking place.

UPDATE None: Takes no action; enforces no restrictions.

Restrict: Checks data in the primary key value of the parent entity
against the foreign key value being updated in the child entity. If the
value does not have a match, prevents the update from taking place.

DELETE None: Allows any record to be deleted from the child entity.