Primary key - The main purpose of the primary key is to provide a mean to identify each record in the table.
- The primary key provides a mean to identify the rows, primary key can be based on one or more column, such as first and last name however in many designs, the primary key is an auto-generated numbers from an identify column.
Au relational database manager systems support primary key. In fact for a table to be considered a relational table and in the first normal form it must have a primary key.
In SQL server primary key is related to its constraints, it has following properties.
There can be one primary key for one table.
Primary key consist of one or more columns.
Primary key enforces entity integrity of the table.
Primary key uniquely identifies a row.
Primary key result in clustered index by default.
Unique Key -
It is also called a unique constraints, it can be used to ensure rows are unique within the database.
Unique key has following prop.
There can be multiple unique keys for a table.
Unique key results in non-clustered unique indexes by default.
One or more columns make up an unique key.
Column may be null but on null pur column is allowed.
Main difference between a primary and unique key is its intended use. A primary key uniquely identifies rows and their we need not bother about unique keys. Main job of unique key allows one to place additional condition on column.