What is a candidate key?

Answer 1

A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. During database design you can pick up one of the candidate keys to be the primary key. For example, in the supplier table supplierid and suppliername can be candidate key but you will only pick up supplierid as the primary key.

Answer 2

Three absolute demands on the candidate key, if it is to be regarded as a possible primary key.

1)    The candidate key must be unique within its domain (the entity it represents, and beyond, if you also intend to access external entities, with is clearly illustrated in this article).
2)    The candidate key can not hold NULLs (NULL is not zero. Zero is a number. NULL is 'unknown value').
3)    The candidate key should never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity.

In 1, we say that the main purpose of the candidate key is to help us to identify one single row in a table, regardless of whether there exists billions of row. This sets high demands on the flexibility in terms of delivering uniqueness.

In 2, we say that a candidate key always, without exception, must hold a value. Without it, we break post 1.

In 3, we say that no matter what happens, inside our business or outside of it, we are not allowed to change the value of the candidate key. Adding an attribute (expanding) the candidate key, would pose a change. Some say that you should be able to change the primary key, and do a 'cascade update' to all underlying tables, ref above.

Actually, 3 it not a formal rule as such, but it is my personal experience through the years that make me claim this as a rule for myself, due to the limitations of today's database products. Cascading updates are very critical operations, not well supported by the different database system vendors.

Answer 3

CANDIDATE KEY

A nominee's for primary key field are know as candidate key.