A candidate key is an attribute (or set of attributes) that identifies an entity. A candidate key is either a primary key or an alternate key. So for example, in the following Student entity, there are two candidate keys: a primary key on Student Number and an alternate key on Student First Name, Student Last Name, and Student Birth Date.
In my data modeling classes, I stress that a candidate key must have three properties:
- Unique. So if we have 100 students we will have 100 unique Student Numbers and 100 unique combinations of Student First Name, Student Last Name, and Student Birth Date. For example, we cannot (on this model) have two students with the same first and last name born on the same date.
- Mandatory. A candidate key must always be populated and have a value, that is for example, the Student Number cannot be null (empty).
- Stable. We cannot update the value of a candidate key. For example, we cannot change Student Number 123 to a different value such as 124.
Someone challenged me recently on the stability property. His comment was, if a logical data model represents a point in time perspective (that is, it is timeless), why is the stability property relevant?
In our physical design, we manage how data changes over time, such as a slowly changing dimension managing changes to the Student Last Name, but is stability important on the logical? Why or why not?
What are your thoughts?