Monday, October 22, 2012

Difference between primary key vs Candidate Key in table - SQL database

Primary key vs Candidate Key
What is difference between primary key and candidate key is another popular SQL and database interview questions which appears in various programming interviews now and then. Concept of primary key and candidate key is not just important from interview point of view but also on designing database and normalization. By the way this is my second post in primary key, In last one we have seen comparison of primary key vs unique key, which is also happens to be one of the frequently asked database question. By definition primary key is a column or collection of columns, which uniquely define a row in a table. Candidate keys are keys which can be primary key and also able to uniquely identify any row in table. In simply terms you may have couple of Candidate keys and you have choose one of them as primary key. This selection part is the most important skill in database design. Since only primary key can have clustered index in table while unique keys can have Nonclustered index, its important to choose right column or collection of columns as primary key. Often I select a column which is most frequently used in Where clause of SELECT query. If you preparing for SQL interview or looking for some good SQL interview question than you can also check difference between Correlated and Noncorrelated subqueries and  When to use truncate vs delete in SQL.

Difference between Candidate Key vs Primary Key

candidate key vs primary key in table sql database Before seeing difference between Candidate key and Primary key let's see some similarities between them in bullets points.


 1) Both Primary and Candidate keys can uniquely identify records in a table on database.

 2) Both Primary and Candidate keys are has constraints UNIQUE and NOT NULL.

 3) Primary key or Candidate keys can be either single column or combination of multiple columns in a table.


Now from interview point of view here is difference between Candidate key and primary key in SQL table on point format for easy to remember :

1) There can be multiple Candidate keys in a table in relation database e.g. Oracle, MySQL, Sybase or MSSQL but only one primary key is permitted.

2) An example of Primary key and Candidate key can be ID and SSN number in a Employee table, Since both can identify each employee uniquely they are candidate key and any one can become primary key. Now if you have to choose between them as primary key, I will go ID as primary key because SSN is sensitive information and may not be allow/not safe to use as String in queries as frequently as ID. Second reason of choosing ID over SSN as primary key can be use of ID as primary tracking ID within organization and its frequent use all over the place. Once you choose a primary key, All candidate key  are like unique keys.

That's all on difference between Primary key and Candidate key in a table. If you understand election well than you can think primary key as elected member among all candidate keys.

Other SQL Interview Question and articles

3 comments:

  1. How about these questions on primary key ?

    What is difference between primary key and surrogate keys ?
    difference between primary key and super key ?
    primary key vs foreign key
    and composite primary key???

    ReplyDelete
  2. "Since only primary key can have clustered index in table while unique keys can have Nonclustered index, ..."

    I think you are mixing up the concept of key with clustered/nonclustered index. As you said, key is used to "uniquely define a row in a table" (identify instead of define?), there is no requirement that the key has to be clustered, or other non-key index cannot be clustered.

    ReplyDelete
  3. The June 25 anon is partly right. In Oracle, only the primary key can be a clustered index (this will then be an Index-Organized Table, rather than the default Heap Table). In SQL Server, the clustered index can be whatever you want.

    No idea in other RDBMS's.

    ReplyDelete

Java67 Headline Animator