A foreign key is an attribute set in one relation based on an identical attribute set that acts as a primary or alternate key in a different relation.
Foreign keys are a special form of Inclusion Compatibilities.
Foreign keys provide a mechanism for performing primary index joins, sometimes referred to as prime key joins.
Foreign keys are also used to maintain referential integrity among related tables in a database (see The Referential Integrity Rule).
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 Designing for Missing Information), you should avoid creating foreign keys that are either wholly or partially null.
- You cannot use BLOB or CLOB columns to define a physical foreign key or other database constraint (see Designing for Database Integrity).
- You cannot define a foreign key for a global temporary trace table. See the information about CREATE GLOBAL TEMPORARY TRACE TABLE in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.