What is the difference b/w Primary Key and Unique Key

Question

I tried to find it out in google but not satisfactory answer is given out there.Can anybody explain the solid difference.
actually if Primary key is used to select data uniquely then what is the need of Unique key?
when should I use a Primary key and when to use a Unique key?

Answer 1

A primary key does not allow nulls, a unique key will allow one null (on sql server and multiple nulls on Oracle) A table can only have one primary key but many unique keys

use primary keys when you want to set up foreign key relationships

Here is a small example with just one column in each table

--primary key table
CREATE TABLE PrimaryTest (id INT PRIMARY KEY NOT NULL)
GO

-- foreign key table
CREATE TABLE ForeignTest (Pkid INT NOT NULL)
GO


--relationship
ALTER TABLE dbo.ForeignTest ADD CONSTRAINT
    FK_ForeignTest_PrimaryTest FOREIGN KEY
    (
    Pkid
    ) REFERENCES dbo.PrimaryTest
    (
    id
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO

insert a row in the primary key table

insert PrimaryTest values(1)

insert a row in the foreign key table with a value that exist in the primary key table

insert ForeignTest values(1)

Now this will fail because value 2 does not exist in the primary key table

insert ForeignTest values(2)

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ForeignTest_PrimaryTest". The conflict occurred in database "aspnetdb", table "dbo.PrimaryTest", column 'id'. The statement has been terminated.

Answer 2

A primary key is a unique key. Both types of key serve to uniquely identify a single row in a table. Many RDBMSs require that one of the unique keys on a table be designated as the "primary key", for several different implementation reasons. In terms of data integrity, there is no difference.

Answer 3

Primary Key and Unique Key are used for different things - understanding what they are for will help you decide when to use them.

The primary key is used to identify a row of data in a table. It is used whenever you need to refer to a particular row, eg. in other tables or by application code etc. In order to identify a row, the values of a PK must be unique. Furthermore, they can't be null, because most dbms treat null as not equal to null (since null typically means "unknown"). A table can have only have one PK. All tables in your databse should have a PK (although this is not enforced by most dbms). PK can span multiple columns.

Unique key constraints are used to ensure that data is not duplicated in two rows in the database. One row in the database is allowed to have null for the value of the unique key constraint. Although a table should have a PK, it need not have any additional unique keys. However, tables can have more than one unique key if that meets your needs. Like PKs, unique keys can span multiple columns.

It is also worth knowing that, by default, many dbms index and physically order tables on disk using the PK. This means that looking up values by their PK is faster than using other values in a row. Typically, however, you can override this behaviour if required.

Answer 4

The term "unique key" is both ambiguous and tautologous. In the relational model, a "key" means a candidate key, which by definition is unique anyway. A primary key is just any one of the candidate keys of a relation. Therefore "unique key" means exactly the same as "candidate key" which means exactly the same as "primary key". There is no difference.

However, SQL has something called a UNIQUE constraint which is subtly different to a SQL PRIMARY KEY constraint - both enforce uniqueness but PRIMARY KEY can only be used once per table. UNIQUE constraints also allow nulls whereas PRIMARY KEY constraints don't.

So the potentially confusing term "unique key" is most often used to mean a key enforced by a UNIQUE constraint. It might even be used to mean a UNIQUE constraint on nullable columns, although that's a very dubious use of the term in my opinion because a set of columns that include nulls cannot be a candidate key so the use of the word "key" for nullable columns isn't really correct and is bound to cause confusion.

Answer 5

Just to add another example:

Think of a table holding user data, where each user has an email address. No two users can have the same email address, so that column becomes a unique key. While it could be the primary key (I have never made a string a primary key), it doesn't have to be.