Referential Integrity Constraints
Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved.
Table lists terms associated with referential integrity constraints.
Referential Integrity Constraint Terms
|Foreign key||The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key.|
|Referenced key||The unique key or primary key of the same or different table that is referenced by a foreign key.|
|Dependent or child table||The table that includes the foreign key. Therefore, it is the table that is dependent on the values present in the referenced unique or primary key.|
|Referenced or parent table||The table that is referenced by the child table’s foreign key. It is this table’s referenced key that determines whether specific inserts or updates are allowed in the child table.|
A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
Figure 1 shows a foreign key defined on the deptno column of the emp table. It guarantees that every value in this column must match a value in the primary key of the dept table (also the deptno column). Therefore, no erroneous department numbers can exist in the deptno column of the emp table.
Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.
Figure 1 Referential Integrity Constraints
Self-Referential Integrity Constraints
Another type of referential integrity constraint, shown in Figure 2, is called a self-referential integrity constraint. This type of foreign key references a parent key in the same table.
In Figure 2, the referential integrity constraint ensures that every value in the mgr column of the emp table corresponds to a value that currently exists in the empno column of the same table, but not necessarily in the same row, because every manager must also be an employee. This integrity constraint eliminates the possibility of erroneous employee numbers in the mgr column.
Figure 2 Single Table Referential Constraints