HOME

TheInfoList



OR:

Referential integrity is a property of data stating that all its references are valid. In the context of
relational database A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
s, it requires that if a value of one attribute (column) of a relation (table) references a value of another attribute (either in the same or a different relation), 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 A foreign key is a set of attributes in a table that refers to the primary key of another table. The foreign key links these two tables. Another way to put it: In the context of relational databases, a foreign key is a set of attributes subject to ...
can only contain either null values or values from a parent table's
primary key In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes ( columns) that uniquely specify a tuple ( row) in a relation ( table). Informally, a primary key is "which attributes identify a recor ...
or a
candidate key A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row (which makes it a superkey), with the additional constraint that removin ...
. 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 system A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
s (RDBMS) 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 A data dictionary, or metadata repository, as defined in the ''IBM Dictionary of Computing'', is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format". ''Oracle'' defines it ...
. The adjective 'referential' describes the action that a
foreign key A foreign key is a set of attributes in a table that refers to the primary key of another table. The foreign key links these two tables. Another way to put it: In the context of relational databases, a foreign key is a set of attributes subject to ...
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 (possibly identical) predicates R and S from a schema is written R _1, ..., A_n\subseteq S _1, ..., B_n/math>, where the A_i, B_i are distinct attributes (column names) of R and S. It implies that the tuples of values appearing in columns A_1, ..., A_n for facts of R must also appear as a tuple of values in columns B_1, ..., B_n for some fact of S. Such constraint is a particular form of
tuple-generating dependency In relational database theory, a tuple-generating dependency (TGD) is a certain kind of constraint on a relational database. It is a subclass of the class of embedded dependencies (EDs). An algorithm known as the chase takes as input an instanc ...
(TGD) where in both the sides of the rule there is only one relational atom. In
first-order logic First-order logic—also known as predicate logic, quantificational logic, and first-order predicate calculus—is a collection of formal systems used in mathematics, philosophy, linguistics, and computer science. First-order logic uses quantifie ...
it is expressible as \forall \vec,\vec . (R(\vec,\vec) \rightarrow \exists \vec . S(\vec,\vec)), where \vec is the vector (whose size is n) of variables shared by R and S, and no variable appears multiple times neither in the TGD's body nor in its head. Logical implication between inclusion dependencies can be axiomatized by inference rules and can be decided by a
PSPACE In computational complexity theory, PSPACE is the set of all decision problems that can be solved by a Turing machine using a polynomial amount of space. Formal definition If we denote by SPACE(''t''(''n'')), the set of all problems that can b ...
algorithm. The problem can be shown to be
PSPACE-complete In computational complexity theory, a decision problem is PSPACE-complete if it can be solved using an amount of memory that is polynomial in the input length (polynomial space) and if every other problem that can be solved in polynomial space can b ...
by reduction from the acceptance problem for a
linear bounded automaton In computer science, a linear bounded automaton (plural linear bounded automata, abbreviated LBA) is a restricted form of Turing machine. Operation A linear bounded automaton is a nondeterministic Turing machine that satisfies the following thre ...
. However, logical implication between dependencies that can be inclusion dependencies or functional dependencies is undecidable by reduction from the word problem for monoids.


Declarative referential integrity

Declarative Referential Integrity (DRI) is one of the techniques in the SQL database programming language to ensure data integrity.


Meaning in SQL (Structured Query Language)

A table (called the referencing table) can refer to a column (or a group of columns) in another table (the referenced table) by using a
foreign key A foreign key is a set of attributes in a table that refers to the primary key of another table. The foreign key links these two tables. Another way to put it: In the context of relational databases, a foreign key is a set of attributes subject to ...
. The referenced column(s) in the referenced table must be under a unique constraint, such as a
primary key In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes ( columns) that uniquely specify a tuple ( row) in a relation ( table). Informally, a primary key is "which attributes identify a recor ...
. Also, self-references are possible (not fully implemented in MS SQL Server though). On inserting a new
row Row or ROW may refer to: Exercise *Rowing, or a form of aquatic movement using oars *Row (weight-lifting), a form of weight-lifting exercise Math *Row vector, a 1 × ''n'' matrix in linear algebra. *Row (database), a single, implicitly structured ...
into the referencing table, the
relational database management system A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
(RDBMS) 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 (forwards a change/delete in the referenced table to the referencing tables), NO ACTION (if the specific row is referenced, changing the key is not allowed) or SET NULL / SET DEFAULT (a changed/deleted key in the referenced table results in setting the referencing values to NULL or to the DEFAULT value if one is specified).


Product specific meaning

In
Microsoft SQL Server Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
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.


See also

* Null pointer dereferencing * Dangling pointer * Domain/key normal form * Entity integrity * Functional dependency * Propagation constraint * Surrogate key * Slowly changing dimension


References


External links


DRI versus Triggers
{{Databases Database management systems Data quality