difference between primary key and unique key

Question

I'm using mysql database. I have a confusion between primary key and unique key.

Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

Solution 1

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"

Solution 2

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:

1. PK creates a Clustered index and UK creates a Non Clustered Index.
2. PK is not null, but UK allows nulls (Note: By Default)
3. There can only be one and only one PK on a table, but there can be multiple UK's
4. 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

Solution 3

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

Solution 4

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.

Solution 5

Difference between Primary Key and Unique Key

+-----------------------------------------+-----------------------------------------------+
|                Primary Key              |                    Unique Key                 |
+-----------------------------------------+-----------------------------------------------+
| Primary Key can't accept null values.   | Unique key can accept only one null value.    |
+-----------------------------------------+-----------------------------------------------+
| By default, Primary key is clustered    | By default, Unique key is a unique            |
| index and data in the database table is | non-clustered index.                          |
| physically organized in the sequence of |                                               |
| clustered index.                        |                                               |
+-----------------------------------------+-----------------------------------------------+
| We can have only one Primary key in a   | We can have more than one unique key in a     |
| table.                                  | table.                                        |
+-----------------------------------------+-----------------------------------------------+
| Primary key can be made foreign key     | In SQL Server, Unique key can be made foreign |
| into another table.                     | key into another table.                       |
+-----------------------------------------+-----------------------------------------------+

View more solutions

Comments

[KNU]

wrt null-ability a good way to distinguish b/w them is PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT

[LCJ]

Take a look at dba.stackexchange.com/questions/15572/…. And stackoverflow.com/questions/1401572/…

[Neville Kuyt]

+1 for mentioning risk of eternal damnation. It's time to introduce theology into relational database theory.

[daremachine]

i) Can only one in a table /// sorry but you can have PFK its not same as PK but both have PK identifier

[ken]

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 : )

[Neeraj Kumar Yadav]

passionforsql.com/… here is the link with detailed description.

[Admin]

What is your mean of 'can be a candidate key' ?

[jinyong lee]

"only single null is allowed" - this is not true, at least not for MySQL.

[Pratik]

Unique key can be null and may not be unique Means ??

[mrd3650]

PK cannot be NULL in SQL Server as well

[Kapil]

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.

[Gokigooooks]

@Kapil doing so beats the whole purpose of using a primary key.

[John]

@PratikCJoshi He probably means that the can be multiple rows with null on the otherwise unique key.

[underscore_d]

@LoveForDroid Of course not. That's why it's called a unique key.

[LoveForDroid]

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

[underscore_d]

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

[LoveForDroid]

@underscore_d It was sqlite3 I was accidentally saving the record twice, hence it was creating same record twice.

[Duy Đặng]

clustered index: the rows are stored physically on the disk in the same order as the index

[Masum]

we can't insert more than one null values in Unique key and it will not allow duplicates also.

[Yusuf Hassan]

"unique can be null and may not be unique". What does may not be unique means here?

[supernova]

@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]

Just got answer to my own comment above. Looks like MySQL allows multiple NULL in unique also so looks like @Mahedi_Hasan used MySQL. stackoverflow.com/questions/3712222/…

[Admin]

@ken: So, means, first diff. is incorrect. As both PK and UK can consist of multiple columns.

[Admin]

@Mr. KB : So, means, first diff. is incorrect. As both PK and UK can consist of multiple columns.

[Admin]

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?

[Poik]

@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: 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]

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.

[Mahedi Hasan Durjoy]

Yap.. you got it i think

[Bishwas Mishra]

Unique constraint can be referenced from foreign key. Primary key is not mandatory.