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

Before discussing about difference between Primary Key and Unique Key, it is important to determine what is key, how it plays a role in business and how it is implemented in SQL / Oracle etc.

As per business prospective: 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 need to record and process information for those business entities. These business entities are identified within whole business domain by a Key.

As per RDBMS prospective: Key(a.k.a Candidate Key), a value or set of values that uniquely identifies entity. For a Db-Table, there are so many keys are exists and might be eligible for Primary Key. So that all keys, primary key, unique key, etc are collectively called as Candidate Key.

For a table DBA selected Candidate Key is called Primary Key, other candidate keys are called secondary keys.

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.

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 with examples: http://dotnetauthorities.blogspot.in/2013/11/Microsoft-SQL-Server-Training-Online-Learning-Classes-Integrity-Constraints-PrimaryKey-Unique-Key_27.html

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 only have one PK. All tables in your database should have a PK (although this is not enforced by most DBMS), and 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.

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.