Primary key vs Candidate Key
What is the difference between primary key and candidate key is another popular SQL and database interview question which appears 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 in 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 that is most frequently used in the Where clause of the SELECT query.
Btw, If you are new to SQL and don't understand fundamentals like primary key, normalization, and basic SQL queries then you can also join these free SQL courses to learn those SQL fundamentals. It's one of the best resources to learn SQL online.
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 that is most frequently used in the Where clause of the SELECT query.
Btw, If you are new to SQL and don't understand fundamentals like primary key, normalization, and basic SQL queries then you can also join these free SQL courses to learn those SQL fundamentals. It's one of the best resources to learn SQL online.
Difference between Candidate Key vs Primary Key
Before seeing the difference between a Candidate key and a Primary key let's see some similarities between them in bullets points.
1. Unique indentification
Both Primary and Candidate keys can uniquely identify records in a table on the database.
2. Constraints
Both Primary and Candidate keys have constraints UNIQUE and NOT NULL.
3. Structure
Primary key or Candidate keys can be either a single column or a combination of multiple columns in a table.
If you preparing for an SQL interview or looking for some good SQL interview questions then you can also check the difference between Correlated and Noncorrelated subqueries and When to use truncate vs delete in SQL.
If you preparing for an SQL interview or looking for some good SQL interview questions then you can also check the difference between Correlated and Noncorrelated subqueries and When to use truncate vs delete in SQL.
Now from the 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. Number of Keys
There can be multiple Candidate keys in a table in relation to the database e.g. Oracle, MySQL, Sybase, or MSSQL but only one primary key is permitted.
2. Example
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 the 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 for 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 that explains a couple of candidate keys (columns that are eligible to become primary keys) and how other keys become alternate keys once a primary key is chosen.
Now if you have to choose between them as the 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 for 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 that explains a couple of candidate keys (columns that 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. If you want to learn more about database design, I suggest you go through these Database and SQL courses.
That's all on the difference between the Primary key and Candidate key in a table. If you understand election well then you can think of 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.
Other SQL Interview Questions and articles
P. S. - If you are looking for an online training/course to learn SQL from scratch, I suggest you check out this list of best SQL online courses. 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.
How about these questions on primary key ?
ReplyDeleteWhat is difference between primary key and surrogate keys ?
difference between primary key and super key ?
primary key vs foreign key
and composite primary key???
"Since only primary key can have clustered index in table while unique keys can have Nonclustered index, ..."
ReplyDeleteI 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.
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.
ReplyDeleteNo idea in other RDBMS's.