Referential Integrity

Information about 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 in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key. For referential integrity to hold, 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. The relational database management system (RDBMS) enforces 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 would be defined by the definition of the referential integrity constraint.

Example

An employee database stores the department in which each employee works. The field "DepartmentNumber" in the Employee table is declared a foreign key, and it refers to the field "Index" in the Department table which is declared a primary key. Referential integrity would be broken by deleting a department from the Department table if employees listed in the Employee table are listed as working for that department, unless those employees are moved to a different department at the same time.

Topics in database management systems (DBMS) • • [ edit]
Concepts Database Database models Database storage Relational model Distributed DBMS ACID Null Relational database Relational algebra Relational calculus Database normalization Referential integrity Relational DBMS Primary key, Foreign key, Surrogate key, Superkey, Candidate key
Objects Trigger View Table Cursor Log Transaction Index Stored procedure Partition	Topics in SQL Select Insert Update Merge Delete Join Union Create Drop Begin work Commit Rollback Truncate Alter
Implementations of database management systems
Types of implementations Relational Flat file Deductive Dimensional Hierarchical Object oriented Object relational Temporal XML data stores
Database products Object-oriented (comparison) Relational (comparison)	Components Query language Query optimizer Query plan ODBC JDBC

A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
..... Click the link for more information.

In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
..... Click the link for more information.

In the relational model, a candidate key of a relvar (relation variable) is a set of attributes of that relvar such that

(1) at all times it holds in the relation assigned to that variable that there are no two distinct tuples with the same values for these attributes and

..... Click the link for more information.

An alternate key (or secondary key) is any candidate key which is not selected to be the primary key (PK).

For example, a relational database with a table "employee" could have attributes like "employee_id", "bank_acct_no", and so on.
..... Click the link for more information.

In the context of relational databases, a foreign key is a referential constraint between two tables.^[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.
..... Click the link for more information.

table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. The columns are identified by name, and the rows are identified by the values appearing in a particular column subset which has been identified as a candidate key.
..... Click the link for more information.

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Relational databases are the most common kind of database in use today (assuming one does not count a file system as a database).
..... Click the link for more information.

database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions.
..... Click the link for more information.

In a relational database, entity integrity is a property that ensures that no records are duplicated and that no attributes that make up the primary key are NULL. It is one of the properties necessary to ensure the consistency of the database.
..... Click the link for more information.

In database systems, a propagation constraint "details what should happen to a related table when we update a row or rows of a target table" (Paul Beynon-Davies, 2004, p.108). Tables are linked using primary key to foreign key relationships.
..... Click the link for more information.

Dangling pointers and wild pointers in computer programming are pointers that do not point to a valid object of the appropriate type, or to a distinguished null pointer value in languages which support this.
..... Click the link for more information.

A database management system (DBMS) is computer software designed for the purpose of managing databases. Typical examples of DBMSs include Oracle, DB2, Microsoft Access, Microsoft SQL Server, PostgreSQL, MySQL, FileMaker and Sybase Adaptive Server Enterprise.
..... Click the link for more information.

A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join.
..... Click the link for more information.

flat file database describes any of various means to encode a data model (most commonly a table) as a plain text file.

Unordered

Unordered storage typically stores the records in the order they are inserted, while having
..... Click the link for more information.

The relational model for database management is a database model based on predicate logic and set theory. It was first formulated and proposed in 1969 by Edgar Codd with aims that included avoiding, without loss of completeness, the need to write computer programs to
..... Click the link for more information.

A distributed database management system is a software system that permits the management of a distributed database and makes the distribution transparent to the users. A distributed database is a collection of multiple, logically interrelated databases distributed over a computer network.
..... Click the link for more information.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
..... Click the link for more information.

Null is a special marker used to indicate that a data value is unknown in the Structured Query Language (SQL). Introduced by the creator of the relational database model, Dr. E.F.
..... Click the link for more information.

Relational algebra, an offshoot of first-order logic, is a set of relations closed under operators. Operators operate on one or more relations to yield a relation. Relational algebra is a part of computer science.
..... Click the link for more information.

The relational calculus refers to the two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and that provide a declarative way to specify database queries.
..... Click the link for more information.

Database normalization is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
..... Click the link for more information.

A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.
..... Click the link for more information.

superkey is defined in the relational model of database organisation as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in
..... Click the link for more information.

In the relational model, a candidate key of a relvar (relation variable) is a set of attributes of that relvar such that

(1) at all times it holds in the relation assigned to that variable that there are no two distinct tuples with the same values for these attributes and

..... Click the link for more information.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.
..... Click the link for more information.

This article is copied from an article on Wikipedia.org - the free encyclopedia created and edited by online user community. The text was not checked or edited by anyone on our staff. Although the vast majority of the wikipedia encyclopedia articles provide accurate and timely information please do not assume the accuracy of any particular article. This article is distributed under the terms of GNU Free Documentation License.

Herod_Archelaus

iPedia Free Information Center