The main purpose of the primary key is to provide a means to identify each record in the table.
The primary key provides a means to identity the row, using data within the row. A primary key can be based on one or more columns, such as first and last name; however, in many designs, the primary key is an auto-generated number from an identity column.
All relational database management systems support primary keys. In fact, for a table to be considered a relational table, and in the first normal form, it must have a primary key.
In SQLServer, the primary key, is referred to as a PRIMARY KEY constraint. A primary key has the following characteristics:
There can only be one primary key for a table.
The primary key consists of one or more columns.
The primary key enforces the entity integrity of the table.
All columns defined must be defined as NOT NULL.
The primary key uniquely identifies a row.
Primary keys result in CLUSTERED unique indexes by default.
As you can see, the primary key is the main key of the table. Its main purpose is to identify each row.
A unique key is also called a unique constraint. A unique constraint can be used to ensure rows are unique within the database. Makes sense, but wait!
Don’t we already do that with the primary key? Yep, we do, but a table may have several sets of columns which you want unique.