Referential integrity is a property of data stating that all its references are valid. In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist. For referential integrity to hold in a relational database, any column in a base table that is declared a foreign key can only contain either null values or values from a parent table's primary key or a candidate key. In other words, when a foreign key value is used it must reference a valid, existing primary key in the parent table. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary. The adjective 'referential' describes the action that a foreign key performs, 'referring' to a linked column in another table. In simple terms, 'referential integrity' guarantees that the target 'referred' to will be found. A lack of referential integrity in a database can lead relational databases to return incomplete data, usually with no indication of an error.
Formalization
An inclusion dependency over two predicates and from a schema is written, where the, are distinct attributes of and. It implies that the tuples of values appearing in columns for facts of must also appear as a tuple of values in columns for some fact of. Logical implication between inclusion dependencies can be axiomatized by inference rules and can be decided by a PSPACE algorithm. The problem can be shown to be PSPACE-complete by reduction from the acceptance problem for a linear bounded automaton. However, logical implication between dependencies that can be inclusion dependencies or functional dependencies is undecidable by reduction from the word problem for monoids.
A table can refer to a column in another table by using a foreign key. The referenced column in the referenced table must be under a unique constraint, such as a primary key. Also, self-references are possible. On inserting a new row into the referencing table, the relational database management system checks if the entered key value exists in the referenced table. If not, no insert is possible. It is also possible to specify DRI actions on UPDATE and DELETE, such as CASCADE, NO ACTION or SET NULL / SET DEFAULT. ANSI/ISO/IEC 9075-1:2003, Information technology—Database languages—SQL—Part 1: Framework ANSI/ISO/IEC 9075-2:2003, Information technology—Database languages—SQL—Part 2: Foundation
Product specific meaning
In Microsoft SQL Server the term DRI also applies to the assigning of permissions to users on a database object. Giving DRI permission to a database user allows them to add foreign key constraints on a table.
