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
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
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???