Difference between a Primary key and a Unique key


Are You looking to difference between a primary key and a unique key? In this article I provided the common difference between them. This articles may becoming a useful resource for beginner or learner.

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.


Comments

Author: Sonu Fernandes22 May 2008 Member Level: Silver   Points : 2

Q:-What's the difference between a primary key and a unique key?

A:- Both Primary Key and Unique Key enforces uniqueness of the column on which they are defined. But by default Primary Key creates a Clustered Index on the column, where are Unique Key creates a Nonclustered Index by default. Another major difference is that, Primary Key doesn't allow NULLs, but Unique Key allows one NULL only.

Author: Siva Prasad22 May 2008 Member Level: Gold   Points : 2

Hello Karthik,

Additional to the RK Fernandes

Unique key allows one null value, but the primary key doesn't allow null value.

Siva Prasad

Author: Ultimaterengan20 Aug 2008 Member Level: Gold   Points : 2

hi,

Primary key and unique key does not allow enter duplicate data.
Primary key and unique are Entity integrity constraints.unique key can be null but primariy key cant be null.
primariy key can be refrenced to other table as FK.We can declare only one primary key in a table but a table can have multiple unique key(column assign

Author: Vijayaragavan.R27 Aug 2008 Member Level: Bronze   Points : 2

PRIMARY refers to a field or a set of fields used to identify each row in a table. UNIQUE refers to a field that cannot be repeated in a table. The primary key is, by necessity, a unique identifier, but any field may be defined as unique. The primary key can be a composite, consisting of more than one field. Each field within a composite key can be repeated, but each combination of fields must be unique.

Author: Vijayaragavan.R27 Aug 2008 Member Level: Bronze   Points : 2

Primary Key - This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key.

Unique Key - Unique and Index are same, the difference is, in Unique, duplicate are not allowed in any circumstances and that is enforced by database server. Primary key(s) qualify to be Unique on basis of their uniqueness.
In case, your table has 2 primary keys means that the 2 fields together form one unique key. Each field by itself may have repeating values, but both primary keys combined together must be unique.

Author: Kolan09 Apr 2010 Member Level: Gold   Points : 2

The column holding the primary key constraint cannot accept
null values.whereas colum holding the unique constraint can
accept null values assume that t3 is a table with two
columns t1 having primary key constraint and t2 having
unique constraint if u try to insert null into t2 it will
accept that values whereas column t1 will not accept null

Author: Martin Andrews17 Aug 2010 Member Level: Bronze   Points : 2

Unique Key Constraint:
The column values should retain uniqueness.
It allows null values in the column.
It will create non-clustered index by default.
Any number of unique constraints can be added to a table.

Primary Key Constraint:
Primary key will create column data uniqueness in the table.
It Wont allow Null values.
By default Primary key will create clustered index.
Only one Primary key can be created for a table.
Multiple columns can be consolidated to form a single primary key.

Author: raja paturu18 Sep 2010 Member Level: Gold   Points : 1

hi,

both are not allow duplicate values



refer the following links

http://www.allinterview.com/showanswers/20536.html

http://www.interviewcorner.com/Answer/Answers.aspx?QuestionId=586&MajorCategoryId=3&MinorCategoryId=22

http://www.techbaba.com/q/285-difference+primary+key+unique+key+sql.aspx

thanks 'n' regards
raja

Author: banu14 Dec 2010 Member Level: Gold   Points : 1

The unique key constraints are used to enforce entity integrity as the primary key constraints.
does allow single null vallue
primary key doesnot allow null values.

Guest Author: Santosh23 Nov 2011

Primary key and Unique key both provide the uniqueness for the column of a database table. A table can have only one Primary key where as more than one Unique keys can be accepted by the table. Primary key never allows null values where as unique key allows.

Author: Sriram24 Nov 2011 Member Level: Gold   Points : 1

Primary Key Restrict Duplicate values and Restrict Null values.Each
Table can have only one Primary Key.Default clustered Index is an Primary Key.

Unique Key restrict Duplicate Values And Allow only one Null Values.Default Non Clustered Index is an unique Key.

thanks
Sriram.R

Author: Sriram24 Nov 2011 Member Level: Gold   Points : 1

Primary Key Restrict Duplicate values and Restrict Null values.Each
Table can have only one Primary Key.Default clustered Index is an Primary Key.

Unique Key restrict Duplicate Values And Allow only one Null Values.Default Non Clustered Index is an unique Key.

Author: Sriram24 Nov 2011 Member Level: Gold   Points : 1

primary Key:
Primary Key restrict Duplicate Values and Null Values .Each Table can have only one Primary Key.By Default clustered Index is the Primary Key.

Unique Key:
Unique Key Restrict Duplicate Values and Allow only one Null value.by Default Non Clustered Index is the Unique Key.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: