Difference between Primary key and Candidate key

Question

I have read about Keys in RDBMS. https://stackoverflow.com/a/6951124/1647112 I however couldn't understand the need to use a candidate key. If a primary key is all that is needed to uniquely identify a row in a table, why is candidate key required? Please give a good example as to state the differences and importance of various keys. Thanks in advance.

I think the OP wants this: Suppose you have three tables. Your main table has as its primary the most important (to the data design) key defined as a primary key. But. The other 2 tables have different primary keys. In order to make effieicent joins between table 1 and 2 or table 1 and 3, you may need to add the common column from the foreign table - a foreign key - to table 1. This is another way of looking at what you are trying to understand. Foreign keys the uniquely idnetify rows in rable 1 — jim mcnamara, May 11 '13 at 20:53

score 5 · Accepted Answer · answered May 11 '13 at 20:48

5

A table can have one or more candidate keys - these are keys that uniquely identify a row in the table.

However, only one of these candidate keys can be chosen to be the primary key.

answered May 11 '13 at 20:48

Oded

489,969
99
883
1,009

2

@ShashankRaghunath - Yes. The one you selected. – Oded May 11 '13 at 20:51
Please be kind enough to tell me, why are super keys used? – Shashank Raghunath May 11 '13 at 20:54
@ShashankRaghunath - Where exactly do you see them used? It is just a term, as far as I know, without much practical use. – Oded May 11 '13 at 20:57
Havent seen them anywhere.I have to learn them for my exams. I simply cant get my head around these keys. – Shashank Raghunath May 11 '13 at 20:59
You can have more than one columns as a PK? Can't you? – rohan-patel Mar 12 '14 at 05:29
@rohan-patel - a **candidate-key** can be more than one field. It follows that so can the chosen PK. – Oded Mar 12 '14 at 08:02

score 0 · Answer 2 · answered May 11 '13 at 21:18

0

From, the above answer i came to this conclusion

Super key(one or more attributes used for selecting one or more rows)

                        ||
                        \/

Candidate key(one or more attributes from super used for selecting a single row)

                        ||  
                        \/

Primary key(one attribute among candidate keys used for selecting a single row)

Am i correct?

answered May 11 '13 at 21:18

Shashank Raghunath

149
3
15

2

The **primary key** is **one of the candidate keys**, picked by you as the database designer. It's not *part* of a candidate key - it's **one of the existing, identified** candidate keys (the **whole** candidate key). – marc_s May 11 '13 at 21:26

Difference between Primary key and Candidate key

2 Answers2