Referential integrity
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 only values from a parent table's primary key or a candidate key. 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.
Contents |
[edit] Benefits of Referential Integrity
[edit] Improved data quality
An obvious benefit is the boost to the quality of data that is stored in a database. There can still be errors, but at least data references are genuine and intact.
[edit] Faster development
Referential integrity is declared. This is much more productive (one or two orders of magnitude) than writing custom programming code.
[edit] Fewer bugs
The declarations of referential integrity are more concise than the equivalent programming code. In essence, such declarations reuse the tried and tested general-purpose code in a database engine, rather than redeveloping the same logic on a case-by-case basis.
[edit] Consistency across applications
Referential integrity ensures the quality of data references across the multiple application programs that may access a database. You will note that the definitions from the Web are expressed in terms of relational databases. However, the principle of referential integrity applies more broadly. Referential integrity applies to both relational and OO databases, as well as programming languages and modeling.
[edit] References
- ^ Mike Chapple. "Referential Integrity". http://databases.about.com/: About.com. http://databases.about.com/cs/administration/g/refintegrity.htm. Retrieved 2011-03-17. "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."
