Keys

Keys identify rows and link tables.

• A candidate key is an attribute (or set of attributes) whose values can uniquely identify every tuple. A relation may have more than one.
• The primary key is the candidate key the designer chooses to identify the tuples. Its values must be unique and can never be NULL.
• The other candidate keys are alternate keys.

Sometimes no single attribute is unique, so two or more together form a composite primary key. In ATTENDANCE, a roll number appears once for every date, and each date appears once for every student, so neither alone is unique; but the pair {RollNumber, AttendanceDate} is.

A foreign key is an attribute whose values come from the primary key of another relation (the referenced relation). It links the two tables. For example, STUDENT.GUID refers to the primary key of GUARDIAN, and ATTENDANCE.RollNumber refers to STUDENT. A foreign key may be NULL, as long as it is not part of its own table's primary key.

So keys do two jobs: the primary key identifies, and the foreign key links.