Difference Between Primary Key and Candidate Key

Primary Key vs Candidate Key

Though primary key is selected from the candidate keys there exist some difference between the primary key and the other candidate keys, which will be discussed in detail in this article. Database designing is one of the most important activity that should be done when maintaining and storing data. During this designing process, different tables with many relationships have to be created. In order to access these tables in a database, different types of keys are used in modern database designing languages such as MYSQL, MSAccess, SQLite, etc. Out of these keys, candidate keys and primary keys have become essential in database designing practices.

What is a Candidate Key?

Candidate key is a single column or set of columns in a table of a database that can be used to uniquely identify any database record without referring to any other data. Each table of a database can have one or more than one candidate keys. A set of candidate keys can be created by using functional dependencies. There are some important features in a candidate key. They are;

• candidate keys should be unique within the domain and they should not contain any NULL values.

• the candidate key should never change, and it must hold the same value for a specific occurrence of an entity.

The main purpose of a candidate key is to help to identify one single row out of millions of rows in a big table. Each candidate key is qualified to become a primary key. However, out of all the candidate keys, the most important and special candidate key will become the primary key of a table and it is the best among the candidate keys.

What is a Primary Key?

A primary key is the best candidate key of a table that is used to uniquely identify records that are stored in a table. When creating a new table in a database we are asked to select a primary key. Therefore, selection of a primary key for a table is the most critical decision that should be taken by a database designer. The most important constraint, which should be considered when deciding a primary key, is that the selected column of the table should only contain unique values, and it should not contain any NULL values. Some of the primary keys that are commonly used when designing tables are Social Security Number (SSN), ID and National Identity Card Number (NIC).

The programmer should remember to choose a primary key carefully because it is difficult to change. Therefore, according to the programmers, the best practice of creating a primary key is to use an internally generated primary key such as Record ID created by AutoNumber data type of MS Access. If we try to insert a record into a table with a primary key that duplicates an existing record, the insertion will fail. The primary key value should not keep changing, so it is more important to keep a static primary key.

A primary key is the best candidate key.

What is the difference between Primary Key and Candidate Key?

• A candidate key is the column that qualifies as unique whereas primary key is the column that uniquely identifies a record.

• A table without candidate keys does not represent any relation.

• There can be many candidate keys for a table in a database, but there should be only one primary key for a table.

• Although the primary key is one of the candidate keys, sometimes it is the only candidate key.

• Once a primary key was selected the other candidate keys become unique keys.

• Practically a candidate key can contain NULL values although it presently does not contain any value. Therefore, the candidate key is not qualified for a primary key because the primary key should not contain any NULL values.

• It may also be possible that candidate keys, which are unique at the moment, may contain duplicate values that disqualify a candidate key from becoming a primary key.

Source: differencebetween.com