Difference between primary key and unique key

Question

I'm using a MySQL database.

In which situations should I create a unique key or a primary key?

wrt null-ability a good way to distinguish b/w them is `PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT` — KNU, Nov 24 '14 at 12:33
Take a look at http://dba.stackexchange.com/questions/15572/differences-between-unique-key-and-primary-key/149580#149580. And http://stackoverflow.com/questions/1401572/what-are-differences-between-index-v-s-key-in-mysql/19345477#19345477 — LCJ, Sep 14 '16 at 18:28

score 282 · Accepted Answer · edited Sep 21 '21 at 06:44

282

Primary Key:

There can only be one primary key constraint in a table
In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
Primary Key is a unique key identifier of the record

Unique Key:

Can be more than one unique key in one table
Unique key can have NULL values
It can be a candidate key
Unique key can be NULL ; multiple rows can have NULL values and therefore may not be considered "unique"

edited Sep 21 '21 at 06:44

danish_wani

862
9
14

answered Nov 12 '12 at 18:15

Mr. KB

2,900
1
13
2

i) Can only one in a table /// sorry but you can have PFK its not same as PK but both have PK identifier – daremachine Mar 09 '13 at 15:40
22

Also want to add on that primary key can be created on multiple columns, e.g. Primary key (CustomerID, ProductID). This is called composite primary key. This is to clarify the first point, as it might be take as it is (read one key => one column ) by new comer to sql : ) – ken Apr 10 '14 at 13:53
http://www.passionforsql.com/difference-between-primary-key-and-unique-key-in-sql-server/ here is the link with detailed description. – Neeraj Kumar Yadav Apr 18 '15 at 07:11
2

What is your mean of 'can be a candidate key' ? – May 28 '15 at 22:23
1

"only single null is allowed" - this is not true, at least not for MySQL. – Jānis Elmeris Aug 29 '15 at 11:56
73

Unique key can be null and may not be unique **Means** ?? – Pratik Joshi Sep 30 '15 at 07:56
21

@PratikCJoshi He probably means that the can be multiple rows with null on the otherwise unique key. – John Jun 10 '16 at 15:01
1

@LoveForDroid Of course not. That's why it's called a _unique_ key. – underscore_d Oct 31 '16 at 15:35
@underscore_d I knew that. I was asking whether it is possible rather than should I do that. The reason why asked that is, in one of my app, there was a bug where same record was saved twice with same unique key. – LoveForDroid Oct 31 '16 at 16:18
@LoveForDroid strange - what DBMS were you using, and was the constraint definitely correct? it certainly sounds like there was a bug in the DBMS if so. – underscore_d Oct 31 '16 at 16:47
@underscore_d It was sqlite3 I was accidentally saving the record twice, hence it was creating same record twice. – LoveForDroid Oct 31 '16 at 18:34
@ken: So, means, first diff. is incorrect. As both PK and UK can consist of multiple columns. – Nov 03 '18 at 08:13
@Mr. KB : So, means, first diff. is incorrect. As both PK and UK can consist of multiple columns. – Nov 03 '18 at 08:13
The info here applies whether the PK (or UNIQUE) is a single column or multiple columns ("composite" key). – Rick James Oct 13 '22 at 01:50

score 89 · Answer 2 · edited Mar 17 '17 at 09:27

89

Unique Key (UK): It's a column or a group of columns that can identify a uniqueness in a row.

Primary Key (PK): It's also a column or group of columns that can identify a uniqueness in a row.

So the Primary key is just another name for unique key, but the default implementation in SQL Server is different for Primary and Unique Key.

By Default:

PK creates a Clustered index and UK creates a Non Clustered Index.
PK is not null, but UK allows nulls (Note: By Default)
There can only be one and only one PK on a table, but there can be multiple UK's
You can override the default implementation depending upon your need.

It really depends what is your aim when deciding whether to create a UK or PK. It follows an analogy like "If there is a team of three people, so all of them are peers, but there will be one of them who will be a pair of peers: PK and UK has similar relation.". I would suggest reading this article: The example given by the author may not seem suitable, but try to get an overall idea.

http://tsqltips.blogspot.com/2012/06/difference-between-unique-key-and.html

edited Mar 17 '17 at 09:27

jatin_ghataliya

144
1
1
16

answered Jun 26 '12 at 00:14

dhi

1,115
8
3

read around 10 webpages, which say, PK can contain more than one column. Then how can there be one and only one PK on a table? – Nov 03 '18 at 08:17
@android A PK with more than one column acts as a single column with respect to the uniqueness. In PostgreSQL at least, this means that a new column (with default name `[table_name]_pkey`) is added to the table (I've heard this referred to as a surrogate key). Source: http://www.postgresqltutorial.com/postgresql-primary-key/ I'm new to all this so I'd appreciate a more knowledgeable poster to point out the nuances I missed. – Poik Nov 15 '18 at 16:51
Okay, it's not a column. I misread. It's a contraint, not a column. There's still the clustered index, but it's over two columns instead of one. And each column in it is not a primary key on its own, instead the whole set is a primary key. So there are not more than one PK in these instances. – Poik Nov 15 '18 at 18:16

score 51 · Answer 3 · edited Mar 17 '17 at 12:07

For an organization or a business, there are so many physical entities (such as people, resources, machines, etc.) and virtual entities (their Tasks, transactions, activities). Typically, business needs to record and process information of those business entities. These business entities are identified within a whole business domain by a Key.

As per RDBMS prospective, Key (a.k.a Candidate Key) is a value or set of values that uniquely identifies an entity.

For a DB-Table, there are so many keys are exist and might be eligible for Primary Key. So that all keys, primary key, unique key, etc are collectively called as Candidate Key. However, DBA selected a key from candidate key for searching records is called Primary key.

Difference between Primary Key and Unique key

1. Behavior: Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry).

2. Indexing: By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.

3. Nullability: Primary key does not include Null values, whereas Unique-key can.

4. Existence: A table can have at most one primary key, but can have multiple Unique-key.

5. Modifiability: You can’t change or delete primary values, but Unique-key values can.

For more information and Examples:

http://dotnetauthorities.blogspot.in/2013/11/Microsoft-SQL-Server-Training-Online-Learning-Classes-Integrity-Constraints-PrimaryKey-Unique-Key_27.html

In 5th point you say we can't change or delete primary values. we for sure can change the primary values in the table by using an update statement. — Kapil, Oct 28 '15 at 13:04
@Kapil doing so beats the whole purpose of using a primary key. — Gokigooooks, Jan 23 '16 at 18:48
clustered index: the rows are stored physically on the disk in the same order as the index — Duy Đặng, Jan 15 '18 at 04:08

Oded · Answer 4 · 2012-03-05T11:48:38.560

27

A primary key must be unique.

A unique key does not have to be the primary key - see candidate key.

That is, there may be more than one combination of columns on a table that can uniquely identify a row - only one of these can be selected as the primary key. The others, though unique are candidate keys.

edited Mar 05 '12 at 11:48

answered Mar 05 '12 at 11:42

Oded

489,969
99
883
1,009

Omer · Answer 5 · 2022-11-25T08:17:40.940

Primary Key	Unique Key
A primary key can't accept `NULL` values	Unique key can accept `NULL` values, so problematic in the context of being unique
A primary key cannot contain duplicate values	A unique key also cannot contain duplicate values
We can have only one primary key in a table	We can have more than one unique key in a table
We can make a primary key from one or more table fields	We can also make a unique key from one or more table fields
By default, a primary key creates a clustered index	By default, a unique key creates a non-clustered unique index
It is used to identify each record in the table	It prevents storing duplicate entries in a column

Yeah, "only one null value per column" is SQL Server but non-standard SQL. PS I converted to markdown table format. — philipxy, Aug 10 '22 at 22:18

score 17 · Answer 6 · answered Mar 05 '12 at 11:44

17

A primary key has the semantic of identifying the row of a database. Therefore there can be only one primary key for a given table, while there can be many unique keys.

Also for the same reason a primary key cannot be NULL (at least in Oracle, not sure about other databases)

Since it identifies the row it should never ever change. Changing primary keys are bound to cause serious pain and probably eternal damnation.

Therefor in most cases you want some artificial id for primary key which isn't used for anything but identifying single rows in the table.

Unique keys on the other hand may change as much as you want.

answered Mar 05 '12 at 11:44

Jens Schauder

77,657
34
181
348

23

+1 for mentioning risk of eternal damnation. It's time to introduce theology into relational database theory. – Neville Kuyt Mar 05 '12 at 11:48
PK cannot be NULL in SQL Server as well – mrd3650 Oct 07 '15 at 14:51

Manuri Perera · Answer 7 · 2019-04-08T08:51:49.547

I know this question is several years old but I'd like to provide an answer to this explaining why rather than how

Purpose of Primary Key: To identify a row in a database uniquely => A row represents a single instance of the entity type modeled by the table. A primary key enforces integrity of an entity, AKA Entity Integrity. Primary Key would be a clustered index i.e. it defines the order in which data is physically stored in a table.

Purpose of Unique Key: Ok, with the Primary Key we have a way to uniquely identify a row. But I have a business need such that, another column/a set of columns should have unique values. Well, technically, given that this column(s) is unique, it can be a candidate to enforce entity integrity. But for all we know, this column can contain data originating from an external organization that I may have a doubt about being unique. I may not trust it to provide entity integrity. I just make it a unique key to fulfill my business requirement.

There you go!

score 9 · Answer 8 · answered Mar 05 '12 at 11:47

9

A Primary key is a unique key.

Each table must have at most ONE primary key but it can have multiple unique key. A primary key is used to uniquely identify a table row. A primary key cannot be NULL since NULL is not a value.

answered Mar 05 '12 at 11:47

Buhake Sindi

87,898
29
167
228

score 8 · Answer 9 · answered Nov 19 '17 at 13:15

8

Think the table name is employe.
Primary key
Primary key can not accept null values. primary key enforces uniqueness of a column. We can have only one Primary key in a table.
Unique key
Unique key can accept null values. unique key also enforces uniqueness of a column.you can think if unique key contains null values then why it can be unique ? yes, though it can accept null values it enforces uniqueness of a column. just have a look on the picture.here Emp_ID is primary and Citizen ID is unique. Hope you understand. We can use multiple unique key in a table.

answered Nov 19 '17 at 13:15

Mahedi Hasan Durjoy

1,031
13
17

1

we can't insert more than one null values in Unique key and it will not allow duplicates also. – Masum Apr 09 '18 at 12:17
@mahedi-hasan Isn't Unique key column should have only one NULL value? How come last two rows in Citizen ID NULL? Am I missing something here? – supernova Sep 10 '18 at 04:32
Just got answer to my own comment above. Looks like MySQL allows multiple NULL in unique also so looks like @Mahedi_Hasan used MySQL. https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints/3712251#3712251 – supernova Sep 10 '18 at 04:38

score 1 · Answer 10 · answered Apr 17 '16 at 07:49

1

If your Database design is such that their is no need of foreign key, then you can go with Unique key( but remember unique key allow single null value ).

If you database demand foreign key then you leave with no choice you have to go with primary key.

To see the difference between unique and primary key visit here

answered Apr 17 '16 at 07:49

user2903536

1,716
18
25

Unique constraint can be referenced from foreign key. Primary key is not mandatory. – Bishwas Mishra Nov 08 '20 at 17:38

ayushs27 · Answer 11 · 2018-07-24T14:23:02.800

Unique key :- It should be used when you have to give unique value.In the case of unique key it means null values are also allowed.Unique keys are those keys which are unique and non similar in that column like for example your pet name.it can be nothing like null and if you are asking in context of database then it must be noted that every null is different from another null in the database.EXCEPT-SQL Server where null=null is true

primary key :- It should be used when you have to give uniquely identify a row.primary is key which unique for every row in a database constraint is that it doesn't allow null in it.so, you might have seen that the database have a column which is auto increment and it is the primary key of the table. plus it can be used as a foreign key in another table.example can be orderId on a order Table,billId in a bill Table.

now coming back to situation when to use it:-

1) primary key in the column which can not be null in the table and you are using as foreign key in another table for creating relationship

2) unique key in table where it doesn't affect in table or in the whole database whether you take the null for the particular column like snacks in the restaurant it is possible you don't take snacks in a restaurant

Gufran Hasan · Answer 12 · 2020-02-13T05:15:53.353

1

difference between Primary Key and Unique Key

Both Primary key and Unique Key are used to uniquely define of a row in a table. Primary Key creates a clustered index of the column whereas a Unique creates an unclustered index of the column.

A Primary Key doesn’t allow NULL value, however a Unique Key does allow one NULL value.

edited Feb 13 '20 at 05:15

answered Nov 26 '18 at 13:03

Gufran Hasan

8,910
7
38
51

score 0 · Answer 13 · answered Nov 11 '14 at 09:50

0

Simply Primary Key is a unique and can't be null, unique can be null and may not be unique.

answered Nov 11 '14 at 09:50

Mohammed F. Ghazo

123
1
10

"unique can be null and may not be unique". What does `may not be unique` means here? – Yusuf Hassan Apr 17 '18 at 10:31

score 0 · Answer 14 · answered Jul 24 '18 at 09:58

Primary Keys

The main purpose of the primary key is to provide a means to identify each record in the table.

The primary key provides a means to identity the row, using data within the row. A primary key can be based on one or more columns, such as first and last name; however, in many designs, the primary key is an auto-generated number from an identity column.

A primary key has the following characteristics:

There can only be one primary key for a table.
The primary key consists of one or more columns.
The primary key enforces the entity integrity of the table.
All columns defined must be defined as NOT NULL.
The primary key uniquely identifies a row.
Primary keys result in CLUSTERED unique indexes by default.

Unique Keys

A unique key is also called a unique constraint. A unique constraint can be used to ensure rows are unique within the database.

Don’t we already do that with the primary key? Yep, we do, but a table may have several sets of columns which you want unique.

In SQL Server the unique key has the following characteristics:

There can be multiple unique keys defined on a table.
Unique Keys result in NONCLUSTERED Unique Indexes by default.
One or more columns make up a unique key.
Column may be NULL, but on one NULL per column is allowed.
A unique constraint can be referenced by a Foreign Key Constraint.

source : here

score 0 · Answer 15 · answered Jun 27 '19 at 17:44

A primary key’s main features are:

It must contain a unique value for each row of data. It cannot contain null values. Only one Primary key in a table.

A Unique key’s main features are:

It can also contain a unique value for each row of data.

It can also contain null values.

Multiple Unique keys in a table.

Difference between primary key and unique key

15 Answers15

Primary Keys

Unique Keys

Linked

Related