solidDB® maintains referential integrity by taking action when referential constraints are violated. Referential constraints are violated, for example, in the following cases:
▪A row that contains an invalid foreign key value is inserted in the referencing table.
▪A foreign key in the referencing table is updated to an invalid value.
▪A row with a referenced primary key is deleted from the referenced table.
▪A referenced primary key is updated in the referenced table.
The referential action is defined as part of the table_constraint_definition in a CREATE TABLE or ALTER TABLE statement. The following actions (options) are available when the constraint is violated:
NO ACTION
This option restricts the operation, or rolls back the operation that violates the referential integrity constraint. The NO ACTION option allows changes to a table that temporarily violate a referential constraint; if you have a requirement that the table state must never violate any constraint even temporarily, use the RESTRICT option.
CASCADE
In the case of operations performed on the referenced table, this option cascades the operations on the referenced table down to the referencing tables. This includes deleting all the referencing rows (a cascading delete) and updating all the referencing foreign-key values (a cascading update).
No cycles are allowed in CASCADE referential actions. An effort to create a cycle composed of foreign keys that have cascading actions results in an error.
For any two tables, at most one CASCADE UPDATE path between them can be defined. The restriction does not apply to CASCADE DELETE.
SET DEFAULT
In the case of operations performed on the referenced table, this option sets the referencing columns to a predefined default value.
SET NULL
In the case of operations performed on the referenced table, this option sets the referencing columns to null.
RESTRICT
This option restricts all operations violating the constraint.
Referential integrity actions sometimes allow changes to a table that temporarily violate a referential constraint. The NO ACTION option allows such violations.
If no option is specified, the default NO ACTION is used.