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 appears in various programming interviews now and then? The 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 about primary keys, In 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 define a row in a table. Candidate keys are keys which 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 primary key can have clustered index in a table while unique keys can have a Nonclustered index, it's important to choose 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.

If you preparing for SQL interview or looking for some good SQL interview question than you can also check the 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 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.


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 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 primary key can be the use of ID as primary tracking ID within the organization and its frequent use all over the place.

Once you choose a primary key, All candidate key 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 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 a 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 Relational Database Design course on Pluralsight, its a free course if you sign up for 10-day free trial.




P.S. - If you are looking for 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 e.g. 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