Database Keys

Primary Key

In relational database design, a primary key is a key used to uniquely identify each row in a table. A primary key comprises a single column or a set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Only one primary key can be in a table.

The values in a primary key are not allowed to be NULL.

Primary keys can be referenced by foreign keys.

See Also

Foreign Key

In the context of relational databases, a foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or a set of columns in another (referenced) table.

The columns in the referencing table must be the primary key or another candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL).

This way, references can be made to link information together, which is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects one master or referenced table to many child or referencing table relationships.

The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. A foreign key is known in SQL:2003 as self-referencing or recursive foreign key.

A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.

Improper foreign key/primary key relationships (or not enforcing those relationships) are often the source of database and data modeling problems.

See Also