What is the candidate key in SQL Server?

Question

Is it a practical concept or just a theoretical concept? How is it different from the primary key?

Answer 1

Candidate key is also called natural key, domain key, or business key. This key is unique. It may not necessarily be primary but it usually is.

Primary key is unique and non-null.

Let's take an example

create table employees (
   employee_id int not null primary key,
   empssn char(9),
   firstname varchar(50) not null,
   lastname varchar(50) not null,
   gender char(1),
   constraint uk_employees_ssn unique (empssn)
);

Employee SSN here is a candidate key. Business-wise it is natural to think that SSN will not be duplicated. It is possible that an employee doesn't have an SSN yet or the employee has not disclosed their SSN yet. Therefore it is unique and nullable.

In this example, we have also chosen to make a different field called employee_id the primary key. The theory is to give employees a sequential numeric value. This allows us to change SSN (I learned while doing a project that under certain circumstances SSN can change). Employee ID is called the surrogate key here. user2864740 has a good comment about the relationship between primary and candidate key.

Also read a nice discussion on social MSDN about keys.

Answer 2

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain.