Keys

The essentials

A key is an attribute or set of attributes that helps identify tuples in a relation or link one relation to another. Because no two tuples can be the same, every relation has at least one key.

Key points

• Candidate key: any attribute (or minimal set of attributes) whose values are unique for every tuple and can therefore identify a tuple. A relation may have more than one candidate key, for example RollNo and AadhaarNo.
• Primary key: the one candidate key chosen by the database designer to identify tuples. It cannot contain NULL and must be unique.
• Alternate key: a candidate key that is not chosen as the primary key.
• Composite key: a primary key made of two or more attributes, for example (ClassSec, RollNo) when roll numbers repeat in different sections.
• Foreign key: an attribute in one relation whose values refer to the primary key of another (or the same) relation. It is used to represent a relationship between relations. A foreign key can contain NULL and can have repeated values.
• The relation containing the primary key is the referenced (parent) relation; the one containing the foreign key is the referencing (child) relation.
• Referential integrity: every non-NULL foreign key value must match an existing primary key value in the referenced relation.

Exam tips

• Number of alternate keys = number of candidate keys − 1.
• If a question gives sample data, check which columns have repeated values; a column with repeats cannot be a candidate key.

Common mistakes

• Thinking a foreign key must be unique. Many employees can share the same DeptNo.
• Thinking only one column can be a primary key. A composite primary key is allowed.