Skip to main content

Return to Answer

Post Timeline

`auto_increment` should be `identity` for SQL Server, but how the key gets populated is immaterial here. Also fixed a missing `unique` keyword on the constraint definition.
Source Link
Aaron Bertrand
Aaron Bertrand
  • 282.4k
  • 37
  • 471
  • 469

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 auto_increment 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.

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 auto_increment primary key,
   empssn char(9),
   firstname varchar(50) not null,
   lastname varchar(50) not null,
   gender char(1),
   constraint uk_employees_ssn (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.

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.

Source Link
zedfoxus
zedfoxus
  • 37.5k
  • 5
  • 68
  • 66

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 auto_increment primary key,
   empssn char(9),
   firstname varchar(50) not null,
   lastname varchar(50) not null,
   gender char(1),
   constraint uk_employees_ssn (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.