Locally cyclic group

2013-10-02T23:02:46Z

169.232.212.26: /* Some facts */

[[File:Referential integrity broken.png|right|350px|thumb|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 which, when satisfied, requires every value of one attribute (column) of a [[relation (database)|relation]] (table) to exist as a value of another attribute in a different (or the same) relation (table).<ref>{{cite web
| accessdate = 2011-03-20
| location = http://databases.about.com/
| publisher = About.com
| title = Referential Integrity
| author = Mike Chapple
| url = http://databases.about.com/cs/administration/g/refintegrity.htm
| 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 field in a [[table (database)|table]] that is declared a [[foreign key]] can contain either a null value, or only 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]].

"Referential" the adjective describes the action that a [[foreign key]] performs, 'referring' to a link field in another table. In simple terms, 'referential integrity' is a guarantee that the target it 'refers' 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. A common problem occurs with relational database tables linked with an [[Join (SQL)|'inner join']] which requires non-NULL values in both tables, a requirement that can only be met through careful design and referential integrity.

==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>.

Logical implication between inclusion dependencies can be axiomatized by inference rules
<ref>[[Serge Abiteboul|Abiteboul]], [[Richard B. Hull|Hull]], [[Victor Vianu|Vianu]].
''Foundations of Databases'' Addison-Wesley, 1994. Section 9.1, p. 193.
Freely available [http://webdam.inria.fr/Alice/ online].
</ref>
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>ibid., p. 196</ref> 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>ibid., p. 199</ref>

==References==
{{reflist}}

==See also==
* [[Dangling pointer]]
* [[Declarative Referential Integrity]]
* [[Domain/key normal form]]
* [[Entity integrity]]
* [[Functional dependency]]
* [[Propagation constraint]]

[[Category:Database management systems]]
[[Category:Data quality]]

formulasearchengine - User contributions [en]

Locally cyclic group