Rules - Teradata Database

Rules

Foreign key values are restricted to three types.

A mirror image, drawn from the same domain, of a primary or alternate key in an associated relation.

Wholly null

Partially null

The partially null case applies to compound foreign keys only, where one or more columns of the key might be null while others might contain references to primary keys in other tables, which are, by definition, non‑null.

Because of the myriad problems nulls present in database management (see Chapter 12: “Designing for Missing Information”), you should avoid creating foreign keys that are either wholly or partially null.

Admitting nulls into the relational model is arguably the only serious error Codd ever made in his development of the theory. In fact, Codd did not introduce nulls into the theory until a full 10 years after he had initially proposed its original version.
Chris Date and his colleagues Hugh Darwen, David McGoveran, and Fabian Pascal have argued forcefully for the elimination of nulls from the relational model based not only on the violation of the principles of the first order predicate logic and set theory they present, but also on the myriad practical difficulties that nulls present to practitioners and to the integrity of any database that permits them. See Chapter 12: “Designing for Missing Information” for a brief summary of their objections to nulls.

You cannot use BLOB or CLOB columns to define a physical foreign key or other database constraint (see Chapter 11: “Designing for Database Integrity”).

You cannot define a foreign key for a global temporary trace table. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics.