Database Management System (DBMS) – Part 9
Data Referential integrity
The referential integrity – often abbreviated as RI – is a form of data integrity. Under the referential integrity is related to the integrity level. In addition to the referential integrity, there are still values integrity (data integrity on the field level) and the data integrity level on record (see Integrity Conditions).
Definition
The referential integrity deals with the correctness of relationships between attributes and the preservation of the uniqueness of their keys.
The referential integrity ensures that in a detail table foreign key only those values which are referenced in the master table of the relationship as a primary key have been created.
Use in database systems
The relationship will advance in a database system. The database system will be relationships between these ratios guarantee. Deletion or modification of operations that would violate the integrity, the database will not run – an associated transaction is rolled back. This will prevent inconsistencies in the data, ie in a single database tables with referential integrity, so check the data with each other. Example: In a warehouse table can only be new products, when previously the corresponding article, with its article number (primary key) was recorded. If the item is deleted, the article must also be held in the storage table is deleted, otherwise the database is inconsistent. (However, with the deletion usefully wait until no more stocks are available.)
Technically, the referential integrity between two relations on a so-called foreign key implements. Both relationships require a common attribute, in a relationship is a primary key and in the other relation of the same or a compatible data type should be. The second relation refers ( “referenced”) on the foreign key to the primary key. The database ensures that primary key exists, and only together with the foreign key is changed or can be deleted.
In connection with tables is the table on which the primary key can be a parent table. The table, which contains the foreign key, called a child table. In the literature, are also sometimes called the German terms parent table and child table and master table and detail table.
For updates of the primary key in the master table, the following options:
* ON UPDATE CASCADE (updated disclosure) means that the update to the foreign keys of the detail table is made.
* ON UPDATE RESTRICT (updated restriction) means: If a master key in the table to be changed, and there are related records in the detail table, then the change is denied.
For deletions in the master table, the following options:
* ON DELETE CASCADE (fighting disclosure) means there will be all records in the detail table is deleted, the reference to that key.
* ON DELETE RESTRICT (Deletion restriction) means: if a record in the master table to be deleted and there are related records in the detail table, then the deletion is denied.
* ON DELETE SET NULL (Nullifier) means: if a record in the master table to be deleted and there are related records in the detail table, then this foreign key NULL registered. Precondition is that the foreign key as an optional column in the table is defined (not NULL).
The various update and delete options are not supported by all RDBMS. The ON UPDATE CASCADE option, for example, is considered by some RDBMS (eg Oracle) is not offered. The reason for this is the view that a primary key is not constant and should be changed.
For the detail table is a foreign key, the restriction that, in these columns only values inserted (INSERT / UPDATE) are allowed in the master table also occur. The only exception is if the foreign key column as a column option is defined. Then can also be inserted NULL, NULL although never as a primary key in the master table will be.
Foreign key
In the context of relational databases, a foreign key is referencing a constraint between two tables. The foreign key identifies a column or set of columns of a table (referential) listing that a column or set of columns of another table (referenced). The values of a record of the columns referential must be present in a single table record referenced. This implies that a record in the referential table does not contain values that do not exist in the referenced table (except in case of NULL values). More records of referential table may point to the same record in the referenced table. In most cases, this is the report “one-to-many table between a father and a child table.
Table referential and can be referenced the same table. A foreign key of this type is also called self-recursive or referential.
A table can have many external keys, and each foreign key can reference a different table.
From Wikipedia, the free encyclopedia. Text is available under the Creative Commons
