Primary Key vs Unique Key. It really not about one or the other. You actually may need both a primary key and unique key in your table. In this article we learn the difference between a primary key and unique key, and why both are important to maintaining a relational database structure.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. Why not learn SQL Server with my free guide Getting Started Using SQL Server?
Before we get into the differences, let’s make sure we understand the definitions of primary and unique keys. We’ll tackle the primary key first.
Primary Key vs Foreign Key – Table of contents
Primary Keys
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. It is a fundamental concept. A database table is truly considered relational and in the first normal form once a primary key is defined.
PRIMARY KEY constraint are used in SQL Server to define a table’s primary keys.
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.
- No NULL columns are allowed.
- 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.
What about unique keys?
Read More: Database Normalization – in Easy to Understand English >>
Unique Keys
Another name for a unique key is unique constraint. To ensure rows are unique within a database, use a unique constraint. 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.
The Employee table below:
Assuming that EmployeeID is the primary key, then we may want to place a unique constraint on GovernmentNumber to ensure each employee has their own number.
In SQL Server the unique key has the following characteristics:
- A table may have zero or more unique key defined.
- Unique Keys result in NONCLUSTERED Unique Indexes by default.
- One or more columns make up a unique key.
- One NULL column per unique key is allowed.
- Foreign Key Constraints can reference Unique Constraints
Primary Key vs Unique Key Comparison
A primary key main job is to uniquely identify a row within a table. The fancy name for this is entity integrity. It an important job. For a table to be truly relational it must have a primary key defined.
So, if the primary key uniquely identified rows, then why bother with unique keys?
Well, the main job of the unique key allows you to place additional unique conditions on your columns. In our example above, we see that though the employee table has a primary key defined as EmployeeID, there’s an opportunity to enforce uniqueness on GovernmentNumber.
So why not just use GovernmentNumber as the primary key?
Good question! The reason I wouldn’t is that the GovernmentNumber is generated by another organization outside your database. What would happen if the number wasn’t unique? ID analytics maintains in 2010 that 20 million Americans have multiple social security number associated with their name. Do you want your primary key to rely on government data? ?
At this point you have a sense of the difference between a primary and unique key, but let’s summarize the differences in the following table:
Now that you know the differences, and see there are also a lot of similarities between primary and unique key, you may have question when to use each. Let me answer those:
So when should you use a primary key?
Always! In my book, every table should have a primary key. It provides the main way (primary) to identify each row.
When should you use a unique key?
I would use a unique key when you have columns you know shouldn’t contain duplicates. This becomes a great way to ensure data validation. In our example, we used the GovernmentNumber, but many others occur.
In fact, you often see a pattern with the primary key is a synthetic value such as an identity value (sequence), therefore, an naturally occurring unique values, such as Account Numbers become candidates for unique keys.
