Keys in a Relational Database

The relational model insists that the tuples within a relation be distinct: no two rows of a table may carry the same values for all their attributes. That requirement is easy to state but has a practical consequence — there must be some way of telling every tuple apart. Concretely, the relation should contain at least one attribute whose data values are distinct (unique) and never NULL. With such an attribute in hand, each tuple of the relation can be uniquely distinguished from every other.

Why must that attribute be non-NULL as well as unique? Because a NULL is an unknown — a row whose "identifying" value is unknown cannot be reliably told apart from any other row. Identification demands values that are both present and unrepeated.

To guarantee this, the relational data model does not leave uniqueness to chance. It imposes restrictions, or constraints, on two things:

• the values the attributes may take — so that the identifying values stay unique and non-NULL; and
• how the contents of one relation may be referred to through another relation — so that when one table points at the rows of a second table, the reference always lands on an identifiable tuple.

These restrictions are not applied as an afterthought; they are specified at the time of defining the database, and the mechanism for specifying them is a set of different types of keys. A key, in this sense, is the formal declaration of which attribute or attributes carry the burden of identifying tuples and of linking relations to one another. …