15.00 - The Referential Integrity Rule - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

The Referential Integrity Rule

If a base relation R has a foreign key FK that matches the primary key PK of base relation S, then every value of FK in R must have one of the following properties:

  • Equal to the value PK of some tuple in S.
  • Wholly null (each value in the attribute set that defines FK is null).
  • This definition presents an obvious contradiction and is not supported by relational theory. By definition, a candidate key (and the FK in a referential relationship must be a candidate key for its relation) must be a unique identifier for its relation, yet a key that is wholly null contains no values, only markers for values that are missing; therefore, it cannot be unique.

    If your enterprise business rules prohibit nulls in foreign key columns, you can enforce a non-null constraint on a column by defining the column with a NOT NULL attribute using either CREATE TABLE or ALTER TABLE.

    In other words, a row cannot exist in a table with a (non-null) value for a referencing column if no equal value exists in its referenced column.

    This relationship is referred to as referential integrity and the act of ensuring that this rule is enforced is referred to as maintaining referential integrity.

    This principle is known as the referential integrity rule, and it is one of the fundamental principles of relational database theory. Besides ensuring database integrity, referential integrity constraints have the added benefit that they are frequently used to optimize join plans when implemented in your physical database design.

    The referential integrity rule applies equally, the necessary changes being made, to all candidate keys, not just the key selected to be the primary key for a relation.