What is the Difference Between a Primary and Unique Key?

In this article, we learn the difference between a primary 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. You can get started using these free tools using my Guide Getting Started Using SQL Server.

Before we get into the difference, let’s make sure we understand the definitions of primary and unique keys. We’ll tackle the primary key first.

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. 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.

What about unique keys?

Unique Keys

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.

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:

• There can be multiple unique keys defined on a table.
• Unique Keys result in NONCLUSTERED Unique Indexes by default.
• One or more columns make up a unique key.
• Column may be NULL, but on one NULL per column is allowed.
• A unique constraint can be referenced by a Foreign Key Constraint.

Primary and Unique Key Comparison

The main difference between a primary and unique key comes from its intended use. 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 a 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, naturally occurring unique values, such as Account Numbers become candidates for unique keys.

The post What is the difference between a primary and unique key? appeared first on Essential SQL.