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

Primary key vs Candidate Key
What is the difference between primary key and candidate key is another popular SQL and database interview questions which appear in various programming interviews now and then? The concept of primary key and candidate key is not just important from the interview point of view but also on designing databases and normalization. By the way, this is my second post about primary keys, In the last one, we have seen a comparison of primary key vs unique key, which also happens to be one of the frequently asked database questions. By definition primary key is a column or collection of columns, which uniquely defines a row in a table. Candidate keys are keys that can be a primary key and also able to uniquely identify any row in the table.

In simple terms, you may have a couple of Candidate keys and you have chosen one of them as a primary key.

This selection part is the most important skill in database design. Since only the primary key can have a clustered index in a table while unique keys can have a Nonclustered index, it's important to choose the right column or collection of columns as a primary key.

Often I select a column which is most frequently used in Where clause of SELECT query.

Btw, If you are new to SQL and don't understand fundamentals like COUNT() function or co-related sub-queries then I highly recommend you go through a comprehensive SQL course like The Complete SQL Bootcamp by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online.





Difference between Candidate Key vs Primary Key

candidate key vs primary key in table sql database Before seeing the difference between a 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 the database.

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

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

If you preparing for SQL interview or looking for some good SQL interview question then you can also check the difference between Correlated and Noncorrelated subqueries and  When to use truncate vs delete in SQL.


Now from interview point of view here is the difference between a 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 to 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 an Employee table, Since both can identify each employee uniquely they are candidate key and anyone can become the primary key.

Now if you have to choose between them as primary key, I will go ID as a primary key because SSN is sensitive information and may not be allowed/not safe to use as String in queries as frequently as ID.

The second reason of choosing ID over SSN as a primary key can be the use of ID as a primary tracking ID within the organization and its frequent use all over the place.

Once you choose a primary key, All candidate keys are like unique keys.

Here is a nice table which explains a couple of candidate keys (columns which are eligible to become primary keys) and how other keys become alternate keys once a primary key is chosen:



In this table Roll_No, Name, and Branch are candidate keys because they have unique values but in reality, Name can be duplicate and Branch can also become duplicate, but Roll_No will be unique, hence it is chosen as a primary key. 

That's all on the difference between the Primary key and Candidate key in a table. If you understand election well than you can think the primary key as an elected member among all candidate keys. Make sure you chose the right primary key, which is the first but very important step in database design. If you want to learn more about database design, I suggest you go through the Relational Database Design course on Pluralsight, its a free course if you sign up for a 10-day free trial.




P.S. - If you are looking for an online training/course to learn SQL from scratch, I suggest you joining Introduction to SQL by Jon Flanders. It's one of the best sources to learn SQL fundamentals like join, subquery, aggregate functions, window functions, grouping data, advanced filtering, and SQL query optimization.

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