Foreign Key Constraints: Referential Integrity

Relational systems allow the enforcement of business rules with constraints, and referential integrity rules form the backbone of relational tables.

Many applications do not use foreign key referential integrity and, instead, rely upon application code to enforce business rules.

Referential-integrity constraints are based on the concept of a foreign key. A foreign key is a column or set of columns in one table that refer to the primary key of another table.

The reference from the foreign key to the primary key is the mechanism whereby the relationships between the occurrences of rows in the two tables are enforced.

One of the most powerful constraints is the referential integrity rule that governs the allowable contents of a column, and in the case of a primary key (PK) and unique key (UK) constraint, in conjunction with foreign keys (FK), define the formal relationship between columns and rows in one table to another.

Creating Foreign key constraints:

ALTER TABLE (table_name)
ADD
CONSTRAINT (foreign key constraint name)
FOREIGN KEY( field name )
REFERENCES primary_table_name
(primary_table_primary_index_field);

Referential integrity (RI) rules ensure that one-to-many relationships are enforced within the relational schema.

The referential integrity, foreign key constraint, identifies a column of a table or view as the foreign key.

This foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table.

The table containing the foreign key is referred to as the child, and the table containing the referenced key is the parent table.

The foreign key and referenced key can be in the same table (parent and child are the same table); otherwise, the parent and child tables must be in the same database.

Referential integrity sometimes needs to be double coded: once for the database and again within the application.

As an example, consider the following tables used to store data about members borrowing books from a library:

BOOKS (BookNo, Title, Author).

MEMBERS (MemberID, MemberName, MemberType).

LOANS (BookNo, MemberID, LoanDate, ReturnDate).

MEMBERTYPES(MemberType, Privileges).

BookNo is a foreign key in the loans table because it is the primary key of the books table. Loans is the foreign table and books the primary table. MemberType is a foreign key in the members table because it is the primary key of the membertypes table.

The relational referential-integrity constraint specifies that either of the following must be enforced for a foreign key

1. The value of the foreign key must be a key value in the corresponding primary table. The value of the foreign key must be undefined (that is, null). This only applies if the foreign key does not participate in the primary key of the foreign table and if the foreign-key columns in the foreign table are allowed to have null values.
2. Check when a row containing an invalid foreign-key value is inserted in the foreign table. Also check when a foreign key in the foreign table is updated to an invalid value.
3. Check when a row with a referenced primary key is deleted from the primary table or when a referenced primary key is updated in the primary table.
4. Restrict the operation, or roll back the operation that violated the referential integrity-constraint.

   In some cases, you can allow the operation, but warn the user of the violation. Relational purists may cringe at this requirement, but it is useful for archive tables and in data warehouse databases. In the case of operations performed on the primary table, set the foreign columns to null.

   In the case of operations performed on the primary table, set the foreign columns to a predefined default value.

5. In the case of operations performed on the primary table, cascade the operations on the primary table down to the foreign tables.

   This includes deleting all the referencing rows (a cascading delete) and updating all the referencing foreign-key values (a cascading or collateral update).

While foreign key constraints should always be used in lieu of application-only coded integrity checking, it's important to realize that using referential integrity constraints adds I/O overhead to insert and update operations.

The additional I/O is attributable to the fact that the foreign key must be validated with the parent table. This overhead can be minimized by assuring that referenced columns are indexed.

During bulk loads, it can be advantageous to disable integrity constraints prior to the load and re-enable the constraints after the load is completed. Once constraints are re-enabled, any errors can be noted and handled.

One of the interesting features of referential integrity constraint is ENABLE NOVALIDATE, which can quickly create an enforced constraint, even when the constraint is believed to be true.

If a database process verifies that a FOREIGN KEY constraint is true then rather than have the database re-verify this FOREIGN KEY constraint, which would require time and database resources, the database administrator could instead create a FOREIGN KEY constraint using ENABLE NOVALIDATE. At this stage, I recommend you to read more on Primary and Foreign keys.

Return to Oracle Database constraints