14

I want to know difference between these two key.

When the Unique key with not null constrain in terms of how they are stored in database

and what difference are there when we making Select,Insert,Update, Delete operation for these keys.

Bhavesh Kachhadiya
  • 3,902
  • 3
  • 15
  • 20
  • http://stackoverflow.com/questions/1308593/what-is-the-difference-between-a-primary-key-and-a-unique-constraint?rq=1 – Ric Dec 05 '13 at 16:07
  • possible duplicate of [difference between primary key and unique key](http://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key) – Tom Thomas Dec 05 '13 at 16:09

5 Answers5

23

A primary key must be unique and non-null, so they're the same from that standpoint. However, a table can only have one primary key, while you can have multiple unique non-null keys.

Most systems also use metadata to tag primary keys separately so that they can be identified by designers, etc.

What are the differences between a primary key and a Unique key with not null constrain in terms of how they are stored in database

If both are either CLUSTERED or NON CLUSTERED then the only difference is metadata in most systems to tag a index as a PK.

what difference are there when we making Select,Insert,Update, Delete operation for these keys

None.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
8

To answer you comment, Yes! In general there is one huge difference in how unique keys and primary keys are stored in SQL Server 2008.

A unique key by default (you can change that) will be created as a non-clustered index and a PK will be created as a clustered index by default (you can change that also).

Non-clustered means it will be stored in a structure "attached" to the table and will consume disk space.

Clustered means the records will be actually stored in that physical order, no consuming disk space, and that's why your table can own just one clustered index. (Just if you are wondering... no, you cannot get 2 non-clustered PK in a table, PK are unique even if they are non-clustered.)

Andriy M
  • 76,112
  • 17
  • 94
  • 154
jean
  • 4,159
  • 4
  • 31
  • 52
  • 1
    One more huge difference. As a rule of thumb inserts are slower and selects faster using clustered indexes (your mileage can vary). and using clustered indexes is a bad idea with GUID fields, using random unordored values ill cause table splits for inserts. – jean Dec 05 '13 at 16:44
  • 1
    @jean: I must say that what you are saying in the comment, while possibly true, is tangential to the original question, which was about the difference between a primary key and a unique constraint, not about the difference between a clustered and a non-clustered index. – Andriy M Dec 05 '13 at 18:28
  • @andriy-m please see OP question at the comments and my answer. It happened the question about diff between unique and PK leads to the question about diff between clustered and nonclustered and since the OP is learning basic concepts about indexes and choosing what to use I felt its important to comment about those things. Maybe the OP can open another question to stop this derivation. – jean Dec 05 '13 at 19:12
0

Primary key cannot be null, there can be only one per table.

Unique keys can contain nulls, and you can have more than one per table.

Andrew
  • 8,445
  • 3
  • 28
  • 46
0

a. Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique key 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.

Glen Joseph
  • 1
0

So to summarise everything I understood from all the given answers, the difference between UNIQUE NOT NULL constraint and PRIMARY KEY constraint is:

  1. There can be AT MOST ONE attribute with Primary Key in the table.
  2. There can be AT LEAST ZERO attributes with Unique Not Null constraint in the table.
  3. A table with Primary Key is created as a Clustered Index.
  4. A table with Unique Not Null is created as a Non-Clustered Index.

What is Clustered Index?

  1. It means that the records are stored in a physical order.
  2. It will not consume extra disk space.
  3. Only 1 Clustered Index can be present in the table at most, hence, only one PRIMARY KEY constraint can be present at most.

What is Non-Clustered Index?

  1. It means there will be a structure which is attached to the table to maintain order.
  2. It will consume extra disk space.
  3. Any number of Non-Clustered Index can be present in the table, hence, any number of UNIQUE NOT NULL constraints can be present in the table.
halfer
  • 19,824
  • 17
  • 99
  • 186
retsmahekaj
  • 1
  • 5