eli5 What is a Candidate Key? [Normalisation Process]

Question

I know it’s a basic question but I can find a simple answer.

Is it just multiple primary keys?

In: 19

Answer 1

It is a key which is eligible to be a primary key, it is also unique. There maybe multiple candidate keys in a table out of that we select one as the primary key, which is the most suitable for a particular table. For an example in Employee table there is employeeId and passportNo, both are unique so they are eligible to be primary key so they are called the candidate key. But out of that employeeId is the most preferred to become the primary key since it is the Employee table.

Answer 2

Primary keys have all unique values. All keys eligible for being selected as primary key are called Candidate keys. For example in a school table Roll No, Registeration No, Student ID No are all candidate keys. You select one from them to be a primary key.

Answer 3

A primary key is a set of attributes chosen by the user to uniquely identify a record. A candidate key is any set of attributes a user could pick to be that primary key. Making poor choices for the primary can serve as a good example.

Perhaps you want to inventory the dresses in your closet. You don’t have many, so simply referring to them by their color is sufficient to specifically refer to a particular dress. The red one. The black one. The blue one. You looked in your closet and noticed color was a candidate key, and selected that to be your primary key.

One day you buy a new blue dress. Oh no, now your primary key won’t work because “the blue one” could refer to either dress. You need to pick a new primary key, so you review your candidate keys. You could pick the order they appear in your closet (dress one, dress two, etc), but then you realize this new dress has pockets while the others don’t. So now you can say “the blue one with pockets” or “the black one without pockets”.

A good database design would try to think forward and cover all the future possibilities, or else simply assign a unique key to each new record in order to guarantee you won’t need to change your system in the future.