World Library  
Flag as Inappropriate
Email this Article

Referential integrity

Article Id: WHEBN0000445718
Reproduction Date:

Title: Referential integrity  
Author: World Heritage Encyclopedia
Language: English
Subject: Data integrity, Data definition language, Master data management, Relational database management systems, Transactions per second
Collection: Data Quality, Database Management Systems
Publisher: World Heritage Encyclopedia
Publication
Date:
 

Referential integrity

An example of a database that has not enforced referential integrity. In this example, there is a foreign key (artist_id) 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 artist_id of 4, which was deleted from the artist table. However, the album "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 (table) to exist as a value of another attribute in a different (or the same) relation (table).[1]

For referential integrity to hold in a relational database, any field in a 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.[2] 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 (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 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.

Formalization

An inclusion dependency over two (possibly identical) predicates R and S from a schema is written R[A_1, ..., A_n] \subseteq S[B_1, ..., B_n], 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.

Logical implication between inclusion dependencies can be axiomatized by inference rules [3] 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.[4] However, logical implication between dependencies that can be inclusion dependencies or functional dependencies is undecidable by reduction from the word problem for monoids.[5]

See also

References

  1. ^
  2. ^ Coronel et al. (2013). Database Systems 10th ed. Cengage Learning, ISBN 978-1-111-96960-8
  3. ^ Abiteboul, Hull, Vianu. Foundations of Databases Addison-Wesley, 1994. Section 9.1, p. 193. Freely available online.
  4. ^ ibid., p. 196
  5. ^ ibid., p. 199
This article was sourced from Creative Commons Attribution-ShareAlike License; additional terms may apply. World Heritage Encyclopedia content is assembled from numerous content providers, Open Access Publishing, and in compliance with The Fair Access to Science and Technology Research Act (FASTR), Wikimedia Foundation, Inc., Public Library of Science, The Encyclopedia of Life, Open Book Publishers (OBP), PubMed, U.S. National Library of Medicine, National Center for Biotechnology Information, U.S. National Library of Medicine, National Institutes of Health (NIH), U.S. Department of Health & Human Services, and USA.gov, which sources content from all federal, state, local, tribal, and territorial government publication portals (.gov, .mil, .edu). Funding for USA.gov and content contributors is made possible from the U.S. Congress, E-Government Act of 2002.
 
Crowd sourced content that is contributed to World Heritage Encyclopedia is peer reviewed and edited by our editorial staff to ensure quality scholarly research articles.
 
By using this site, you agree to the Terms of Use and Privacy Policy. World Heritage Encyclopedia™ is a registered trademark of the World Public Library Association, a non-profit organization.
 


Copyright © World Library Foundation. All rights reserved. eBooks from Project Gutenberg are sponsored by the World Library Foundation,
a 501c(4) Member's Support Non-Profit Organization, and is NOT affiliated with any governmental agency or department.