What's the point of a candidate key?

user_54790 0
Viewed 10288

I'm fairly new to database management and this question never seems to be answered in more than one sentence. All other SO answers say "A candidate key is a minimal super key." That means nothing to me.

A candidate key is supposed to specify uniqueness of a db record, correct? And a primary key is a candidate key. If a primary key already specifies uniqueness, what's point of adding more candidate keys?

I have seen example records like the following:

Employee(ID, Name, PhoneNumber)

where ID is the primary key and PhoneNumber is a candidate key. From what I see, the ID is enough to specify the uniqueness of an employee record. Although PhoneNumbers are (probably) unique, specifying them as a candidate key does not seem "minimal" to me.

entity-relationship relational-database database-design database
9 Answers

In the RM (relational model): A superkey is a unique column set. A CK (candidate key) is a superkey containing no smaller superkey. It is a candidate for being a PK (primary key). A PK is just some CK you decided to call the PK. The other CKs are AKs (alternate keys).

A SQL PK is a UNIQUE NOT NULL, not necessarily a CK/PK. So we can say an SQL CK is UNIQUE NOT NULL, a candidate for being declared as SQL PK, and we can call the others SQL AKs. In an SQL context you have to ask people what they mean when they use these terms with different meanings in the RM & SQL.

user_34040970

In nutshell: CANDIDATE KEY is a minimal SUPER KEY.

Where Super key is the combination of columns(or attributes) that uniquely identify any record(or tuple) in a relation(table) in RDBMS.

For instance, consider the following dependencies in a table having columns A, B, C and D (Giving this table just for a quick example so not covering all dependencies that R could have).

Attribute set (Determinant)---Can Identify--->(Dependent)

A-----> AD

B-----> ABCD

C-----> CD

AC----->ACD

AB----->ABCD

ABC----->ABCD

BCD----->ABCD

Now, B, AB, ABC, BCD identifies all columns so those four qualify for the super key.

But, B⊂AB; B⊂ABC; B⊂BCD hence AB, ABC, and BCD disqualified for CANDIDATE KEY as their subsets could identify the relation, so they aren't minimal and hence only B is the candidate key, not the others.

Thanks for asking.

user_115249790
Related