| Referential |
The referential integrity rule states that a child instance cannot exist if
there is no corresponding parent instance. In general terms, a parent instance
cannot be deleted if one or more child instances exist nor may a child
instance be created if no parent exists. There are three categories of
referential rules: insert, delete, and update. Database designers must either
specify a rule or or accept the default rule for each category.
| Insert |
- Dependent* -- A child instance can be inserted only if a matching
parent instance exists. This is the normal default rule.
- Default -- A child instance can always be inserted. If no matching
parent exists, the foreign key is set to a default or null value.
- Automatic -- A child instance can always be inserted. If no matching
parent exists, one is created on the fly.
- No Effect -- A child instance can always be inserted even if no
matching parent instances exist. Warning! Causes referential integrity issues.
- Customized -- A child instance can only be inserted if specific constraints are met. Then, the
dependent, default, automatic, or no effect rule is applied.
|
| Delete |
- Restrict* -- A parent instance can be deleted only if no matching child instance exists. This is the normal default rule.
- Cascade* -- Deleting a parent instance automatically deletes all matching child instances.
- Default -- Deleting a parent instance automatically updates the foreign key of all matching
child instances to a default or null value.
- No Effect -- A parent instance can always be deleted even if child instances exist. Warning! Causes referential integrity issues.
- Customized -- A parent instance can only be deleted if specific constraints are met. Then, the cascade, default, or no effect
rule is applied.
|
| Update |
- Restrict* -- A parent instance cannot be updated if one or more matching child instances
exist. This is the normal default rule.
- Cascade* -- Updating a parent instance automatically updates the
foreign key of all matching child instances.
- Default -- Updating a parent instance automatically updates the foreign key of all matching instances to a default or null value.
- No Effect -- A parent instance can always be updated even if child instances exist. Warning! Causes referential integrity issues.
- Customized -- A parent instance can only be updated if specific constraints are met. Then, the child instance is subjected to the cascade, default, or no effect rule.
|
| * valid in SQL Server |
|