Referential and Self-Referential Integrity Constraints

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

Term Definition
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

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

Single Table Referential Constraints

Source: http://docs.oracle.com/cd/B28359_01/server.111/b28318/data_int.htm

Advertisements

About kaanmutlu

Software Developer - Computer Engineer from Istanbul, Turkey
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s