Difference between Primary Key, Unique Key and candidate key

Question

What is difference between Primary Key, Unique Key and candidate key?

Clive

Answer 1

Candidate key is a minimal (i.e. irreducible) key, unique key is a pleonasm, and primary key is an obsoleted concept still surviving from the days when people thought it possible for one key to be "more unique" than any of the others.

Laureenlaurel

Can a candidate key be composite, I am quite confused as some sources say that it should be a single column and some say that it can be composite? – Station

Keys can be composite. Nothing wrong with them. If the number of attributes gets to be rather large (think, 4 or more) then you might start running into practical disadvantages, but even then there is still nothing wrong with them, at the fundamental theoretical level. – Laureenlaurel

Answer 2

Super Key is the set of one or more column (ie attributes) which uniquely identifies a record.

Candidate key is a minimal Super key.(it mean we cant remove any attributes from it otherwise it will not remain Superkey anymore).

Primary Key is a arbitrary selected Candidate key. There must be only and only One primary key. We can choose any candidate key as a Primary key. Other candidate keys which are not chosen as Primary are called Alternate Keys.

If Primary Key have more then one column (or attributes) ,it is called Composite Key.

Crimp

Like to know your view on 'Unique Key'!! – Sporozoite

Unique key allows NULL values. However, primary or candidate keys don't allow NULL values. – Lemuelah

Answer 3

Candidate keys - It is a column that can uniquely identify all columns for a specific row or instance of your database table. Your passport ID is a good example of a Candidate key and when using this ID you can retrieve a persons name, surname and other details related to that specific person.

Primary key - There may be a few Candidate keys on a specific database table which is unique and can be used to identify a specific instance in a table e.g. Vehicle registration number, Chassis number, engine serial number etc... but remember there can only be one candidate key that will be used as a primary key. Difference between Candidate Key vs Primary Key:- ) 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.

Sampan

This is an incomplete answer. Unique key isn't mentioned anywhere. – Lemuelah

Answer 4

Google does not work anymore?

http://en.wikipedia.org/wiki/Candidate_key

http://en.wikipedia.org/wiki/Unique_key

Dissect

question wants concrete difference between them. – Walking

Answer 5

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. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.

For more articles

Deter

Answer 6

All the answers are missing physical representation and some of them are incomplete. So here is the difference by graphical representation

---

Explanation

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.

Example : Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In above diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key

Alternate key is a key that can be used as a primary key. Basically it is a candidate key that is currently not being used as the primary key.

Example: In above diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key

Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer the article Difference between primary key and unique key.

Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.

Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

source

Cuprum

as well as almost-A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. – Thief

@Cuprum What is difference between candidate key, unique key and composite key? You have written similar definition to these three terminologies. Please explain. Thanks in advance. – Heterography

Answer 7

A composite key is a primary key which has more than 1 column. For example consider the many to many relation between student and courses. Here we need another table , lets say studentcourses.

This studentcourses table will have student_id and course_id as its columns. This will form your composite key.

Candidate key is a set of columns which can uniquely identify the values in a table and can act as a unique key. One of these candidate keys will become the primary key and the rest will become alternate keys.

Unique key as the name suggests , is used to uniquely identify a value in a table. For example Emp_id etc. Every candidate key will act as a unique key. Unique key can never be a NULL value.

Pupiparous

Actually, PK cannot accept NULL values, buy Unique Key CAN BE NULL. – Caloyer

Answer 8

Before proceeding to the difference, it is important to understand the definition of each term.

CANDIDATE KEY: A minimal set of columns that can uniquely identify every row in a table. Minimal means that no further reduction of columns is possible without jeopardizing the ability to uniquely identify a row in a table. Every table must have at least one candidate key but at the same time can have several. Specific candidate keys are sometimes called primary keys, secondary keys, or alternate keys.

PRIMARY KEY: A set of columns—and by implication, the index based on this set of columns—that can uniquely identify every row in a table. As such, it must be a unique index that does not contain any NULL values. There can only be one primary key in a table. A primary key is chosen from candidate key. Primary key is thus a subset of candidate key or can be viewed as a specialized case of candidate key.

For best performance, choose the primary key columns carefully based on the most performance-critical queries. Choose primary columns that are rarely or never updated because modifying the columns of the primary key index is an expensive operation. When choosing primary key values, consider using arbitrary values rather than relying on values derived from some other source.

UNIQUE KEY: A unique key is a column or set of columns that comprises unique index. Unique index is an index on a column or set of columns that have a unique constraint (prevents duplicate values). Unique key can also store NULL values.

[Note: MySQL allows multiple NULL values in a column with unique constraint, but this may not be true for all databases.]

Further reading on NULL behaviour:

• Does MySQL ignore null values on unique constraints?
• How do I create a unique constraint that also allows nulls?

Key difference between candidate key and primary key

Primary key is a specialized subset of candidate key and there can only be one primary key in a table whereas there can be multiple candidate keys in a table.

Key differences between primary key and unique key

• The primary key does not store null values, whereas the unique key does.

• A table can only have one primary key, whereas it can have multiple unique keys.

• The primary key does not allow you to delete or modify the data. On the other hand, a unique key does.

• The primary key’s purpose is to enforce entity integrity, whereas the unique key’s purpose is to enforce unique data.

References: MySQL Reference Manual

Esquire

Answer 9

Candidate Key: a column/attribute (or set of attributes) that uniquely identifies tuples/rows in a relation/table.

Primary Key: is also a column/attribute (or set of attributes) that uniquely identifies tuples/rows in a relation/table.

Unique Keys: Candidate keys that are not Primary key (so, unique keys can also uniquely identify a row in a table).

Differences:

• All candidate keys of a table can uniquely identify rows/tuples of a relation/table, but only one of them is used as Primary Key (PK), i.e., a table can have more than one candidate key, but it can have one and only one PK.
• PK cannot include NULL values, but candidate keys can have NULL values
• a PK is also a candidate key, but a candidate key may not be a PK.

Caloyer