Tables Referential Integrity

Referential integrity

Referential integrity is a tool, that allows you to control relations between records in related tables.

Referential integrity is defined as a relationship of two tables. The table that has the referential integrity rule specified, is called inferior table. Table specified in the integrity constraint is called superior table. A column or a group of columns is set in both tables as a key. Rules of referential integrity guarantee, that for each record in the inferior table whose key is not NULL there exists a record in the superior table with the same key. The key in the superior table has to be a unique key, the key in the inferior table has to be some key.

Referential integrity does the following when manipulating data:

when adding a record into the inferior table (or updating a value of the key column), existence of the same key in the superior table is checked.
when deleting a value (by deleting or updating a value) in the superior value, existence of the same key in the inferior table is checked.

Breaking the first rule invokes an error and transaction rollback. Breaking the second rule may cause an error and transaction rollback or data modification in the inferior table according to the defined actions.

Referential integrity rule is specified as follows:

referential_integrity_constraint ::= FOREIGN KEY (column_name {, column_name}... ) 
	REFERENCES [scheme.]table_name [ column_name {, column_name}... ] 
	[ ON UPDATE referential_integrity_action ] [ ON DELETE referential_integrity_action ] 
	[ evaluation ]
referential_integrity_action ::=  SET NULL | SET DEFAULT | CASCADE | NO ACTION

The first group of column names specifies the key in the inferior table. The second group specifies the key in the superior table. If not specified, the primary key of the superior table is used. Keys from both tables must be of the same type.

Referential integrity rule connecting one column from both tables can be alternatively described as a column property.

Active Referential Integrity

Referential integrity rules may specify what action the server will take, if the referential integrity is broken as a result of deleting or updating a record in the superior table. The referential integrity action specified in the ON DELETE or ON UPDATE clause is executed in such case:

SET NULL - NULL values are set into the key columns of records in the inferior table with the same key value;
SET DEFAULT - default values are set into the key columns of records in the inferior table with the same key value;
CASCADE - the modifications done in the superior table are done in the inferior table as well on the columns with the same key value: deleting a superior record will result in deleting of all inferior records, updating the key values in a superior record will result in updating all inferior record key values.
NO ACTION or action not specified - an error occurs - sqlstate 40006 (REFERENTIAL_CONSTRAIN) and transaction is rolled back.

In all these cases referential integrity rule are kept intact, and the rule is nor broken.

Privileges for modification inferior table are not checked when executing the actions of referential integrity. Action is executed if the client has the privilege to make the change, that invokes the action, in the superior table.

Trigger execution by referential integrity action depends on the compatibility attributes setting using the Set_sql_option (SQLOPT_NO_REFINT_TRIGGERS (4096)) function. If the triggers are executed, then the trigger execution due to the referential integrity may be detected when the trigger is running using the system variable @@ACTIVE_RI_IN_PROGRESS.

Example: table with the condition number >0 and active referential integrity into to the company classifier (superior table COMPANIES)

CREATE TABLE `ORDERS` (
`COMPANY_ID` INTEGER ,
`ORDERED` NUMERIC(14,2) ,
...
CONSTRAINT `Companies` INDEX (`COMPANY_ID`),
CONSTRAINT `Check_gt0` CHECK (ordered>0) INITIALLY IMMEDIATE,
CONSTRAINT `Company-Obj` FOREIGN KEY (`COMPANY_ID`) REFERENCES `COMPANIES`(`COMPANY_ID`) 
	ON UPDATE CASCADE ON DELETE CASCADE)

When are the Integrity Constraints Checked?

Server may check integrity constraints either at the statement completion or at the transaction completion. The check time is specified with the INITIALLY IMMEDIATE or INITIALLY DEFERRED keywords.

Integrity Constraints in Tables

Database Tables

When do the Integrity Constraint Checks Take Place?