15.00 - Entity Integrity and Foreign Key Nulls - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Entity Integrity and Foreign Key Nulls

Permitting a foreign key to be wholly null seems to contradict the entity integrity rule (see “Rules for Primary Keys” on page 92). This property is, to be sure, somewhat ad hoc, but it can have some practical usefulness. For example, consider the following case: an employee is working for some enterprise but has not yet been assigned to a department. Assuming that Department Number is a FK in the Employee relation, you can readily see that even when all the remaining information required to create an Employee relation tuple for the employee in question is available, you cannot create such a tuple in the Employee relation unless you also allow the FK to be null.

The tuple in question should be updated with the appropriate Department Number data just as soon as it is available, but allowing the null FK permits the enterprise to pay this employee even though she is not yet assigned to a department.

Alternatively, the problem can be avoided entirely with a minor change in the logical design of the database. See “Redesigning the Database to Eliminate the Need for Nulls” on page 680 for an example.

As another alternative, you can assign default values to the FK field set and then update with the appropriate Department Number as soon as you know what it is. SQL facilities for assigning default values to columns are described in SQL Data Types and Literals.