Editing Referential integrity

{{Short description|Where all data references are valid}}
[[File:Referential integrity broken.png|thumb|350px|An example of a database that has not enforced '''referential integrity'''. In this example, there is a foreign key (<code>artist_id</code>) value in the album table that references a non-existent artist — in other words there is a [[foreign key]] value with no corresponding [[primary key]] value in the referenced table. What happened here was that there was an artist called "[[Aerosmith]]", with an <code>artist_id</code> of <code>4</code>, which was deleted from the artist table. However, the album "[[Eat the Rich (Aerosmith song)|Eat the Rich]]" referred to this artist. With referential integrity enforced, this would not have been possible.]]
'''Referential integrity''' is a property of data stating that all its references are valid.  In the context of [[relational database]]s, it requires that if a value of one attribute (column) of a [[relation (database)|relation]] (table) references a value of another attribute (either in the same or a different relation), then the referenced value must exist.<ref>{{cite web |last=Chapple |first=Mike |title=Referential Integrity |work=About.com Tech |publisher=About.com |url=http://databases.about.com/cs/administration/g/refintegrity.htm |access-date = 2011-03-20 |quote='''Definition''': Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.}}</ref>

For referential integrity to hold in a relational database, any column in a base [[table (database)|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]].<ref>Coronel et al. (2013). Database Systems 10th ed. Cengage Learning, {{ISBN|978-1-111-96960-8}}</ref> 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]]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]].

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 (possibly identical) predicates <math>R</math> and <math>S</math> from a schema is written <math>R[A_1, ..., A_n] \subseteq S[B_1, ..., B_n]</math>, where the <math>A_i</math>, <math>B_i</math> are distinct attributes (column names) of <math>R</math> and <math>S</math>. It implies that the tuples of values appearing in columns <math>A_1, ..., A_n</math> for facts of <math>R</math> must also appear as a tuple of values in columns <math>B_1, ..., B_n</math> for some fact of <math>S</math>.

Such constraint is a particular form of [[tuple-generating dependency]] (TGD) where in both the sides of the rule there is only one relational atom.<ref>{{cite web|url=https://www.knaw.nl/shared/resources/actueel/bestanden/kolaitis.pdf#page=5|title=A Tutorial on Database Dependencies|publisher=University of California Santa Cruz & IBM Research - Almaden|last=Kolaitis|first=Phokion G.|date=|access-date=2021-12-10}}</ref>  In [[first-order logic]] it is expressible as <math>\forall \vec{x},\vec{y} . (R(\vec{x},\vec{y}) \rightarrow \exists \vec{z} . S(\vec{x},\vec{z}))</math>, where <math>\vec{x}</math> is the vector (whose size is <math>n</math>) of variables shared by <math>R</math> and <math>S</math>, 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<ref name=foundations-db>{{cite book |last1=Abiteboul |first1=Serge |author-link1=Serge Abiteboul |last2=Hull |first2=Richard B. |last3=Vianu |first3=Victor |author-link3=Victor Vianu |date=1994 |title=Foundations of Databases |chapter=9. Inclusion Dependency |publisher=Addison-Wesley |pages=192–199 |url=http://webdam.inria.fr/Alice/}}</ref>{{rp|193}}
and can be [[decidable language|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]].<ref name=foundations-db />{{rp|196}} However, logical implication between dependencies that can be inclusion dependencies or [[functional dependencies]] is undecidable by reduction from the [[word problem (computability)|word problem]] for [[monoids]].<ref name=foundations-db />{{rp|199}}

==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=== 
{{main|Foreign key}}
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]]. The referenced column(s) in the referenced table must be under a unique constraint, such as a [[primary key]]. Also, self-references are possible (not fully implemented in MS SQL Server though<ref>{{cite web |author=Microsoft Support |date=2007-02-11 |title=Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths |publisher=microsoft.com |url=http://support.microsoft.com/kb/321843/en-us |access-date=2009-01-24}}</ref>). On [[Insert (SQL)|inserting]] a new [[Row (database)|row]] into the referencing table, the [[relational database management system]] (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 (SQL)|UPDATE]] and [[Delete (SQL)|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).<ref>
ANSI/ISO/IEC 9075-1:2003, Information technology—Database languages—SQL
* Part 1: Framework (SQL/Framework)
* Part 2: Foundation (SQL/Foundation)
</ref>

===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.<ref>{{cite web |last=Chigrik |first=Alexander |date=2003-08-13 |title=Managing Users Permissions on SQL Server |publisher=Database Journal |url=http://www.databasejournal.com/features/mssql/article.php/2246271 |access-date=2006-12-17}}</ref>

==See also==
*[[Null_pointer#Null_dereferencing|Null pointer dereferencing]]
*[[Dangling pointer]]
*[[Data integrity]]
*[[Domain/key normal form]]
*[[Entity integrity]]
*[[Functional dependency]]
*[[Propagation constraint]]
*[[Surrogate key]]
*[[Slowly changing dimension]]

==References==
{{Reflist}}

==External links==
* [http://www.cvalde.net/document/declaRefIntegVsTrig.htm DRI versus Triggers] ([https://web.archive.org/web/20110723065232/http://www.cvalde.net/document/declaRefIntegVsTrig.htm archived])

{{Databases}}
[[Category:Database management systems]]
[[Category:Data quality]]