In SQL, a UNIQUE key is a column or set of columns that can uniquely identify a row in a table. These are also candidate keys. Only one candidate key can become the primary key for a table. All other candidate keys can then be referred to as UNIQUE keys, given they can uniquely identify a row in a table.

To enforce the uniqueness, we can create a UNIQUE constraint. This ensures that all values in a column or a group of columns are unique across all rows in a table. In other words, no duplicate values are allowed. This helps maintain data integrity by enforcing uniqueness constraints.

As mentioned, a table can have multiple UNIQUE keys, but only one primary key. And unlike the primary key, the column values of a UNIQUE key can be NULL (though in some DBMSs, NULLs are treated as non-duplicate).

Why Use a UNIQUE Key?

When we refer to a “UNIQUE key”, there’s usually an assumption that the column has a UNIQUE constraint or UNIQUE index applied to it. This is because, without such a constraint or index, we have no way of enforcing the “uniqueness” of the values in the column. In that case, it would be a misnomer to call it a UNIQUE key (because it could potentially contain duplicate values).

So with that in mind, a UNIQUE key is vital when you need to ensure the uniqueness of data that is not a primary key. For instance, you might have an Email column in a Users table, where no two users should share the same email address. By making the Email column a UNIQUE key, you ensure that each row will have a distinct value in that column.

Other scenarios where UNIQUE keys are useful include:

• Usernames on a platform (e.g., username column).
• Phone Numbers where no two users can share a phone number.
• Social Security Numbers or National IDs (e.g., ssn column).
• Any other field where we need to prevent duplicates but don’t use it as the primary key.

Characteristics of a UNIQUE Key

UNIQUE keys are a common key type. Actually, the term key itself implies uniqueness. To that extent, the term “unique key” is a pleonasm, kind of like someone referring to an ATM as an “ATM machine” (ATM stands for Automatic Teller Machine).

So with that in mind, here are some characteristics that UNIQUE keys possess:

• Multiple UNIQUE Keys: A table can have more than one UNIQUE key. For example, a Users table can have a UNIQUE key on both the email and username columns.
• NULL Values: Some DBMSs allow multiple NULL values in a UNIQUE key column, while others allow only one. The SQL standard permits NULL values because NULL is treated as an “unknown” value, meaning two NULLs are not considered duplicates. However, some databases only allow a single NULL.
• Distinct from Primary Key: A UNIQUE key is different from a primary key in several ways:
  • Primary Key: Enforces uniqueness and non-nullability. Each table can only have one primary key.
  • UNIQUE Key: Ensures uniqueness but allows NULLs (depending on DBMS). A table can have multiple UNIQUE keys. A UNIQUE key is a candidate key that wasn’t chosen as the primary key.
• Indexing: Most DBMSs automatically create an index when a UNIQUE constraint is added to a column. This index improves query performance when searching for unique values and enforces the uniqueness constraint.

Example of Defining a UNIQUE Key

We can define UNIQUE keys at the time of table creation and we can also add them to existing tables.

Here’s an example of defining a UNIQUE key at the time we create the table:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

In this example, we created a UNIQUE constraint against the Email column. This means that no two users can have the same email address. The column is therefore a UNIQUE key.

Suppose we didn’t add that UNIQUE constraint, but we now want to make the column a UNIQUE key. We can therefore do the following:

ALTER TABLE Users ADD UNIQUE (Email);

This adds a UNIQUE constraint to the Email column after the table has already been created, and the column is now a UNIQUE key.

Composite UNIQUE Keys

A UNIQUE key can also span multiple columns, forming a composite UNIQUE key. This is useful when the uniqueness of a row is determined by a combination of columns.

Let’s say we have a ProductOrders table where each product order is linked to both a ProductID and OrderID, and we want to ensure that each product can only appear once per order.

We can do the following:

CREATE TABLE ProductOrders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    UNIQUE (OrderID, ProductID)
);

This implements a UNIQUE constraint where the UNIQUE key is the combination of the OrderID and ProductID columns. The values across both columns must be unique, meaning the same product cannot be added multiple times to the same order. However, the same product can be used in other orders. The composite UNIQUE key ensures that we still have this flexibility. If we were to apply a UNIQUE index to just the ProductID column, then we wouldn’t be able to have the same product in other orders.

UNIQUE Key vs. UNIQUE Index

A common source of confusion is the distinction between a UNIQUE key and a UNIQUE index. In most DBMSs, when you create a UNIQUE constraint, a corresponding UNIQUE index is automatically created to enforce the constraint. However, a UNIQUE index can exist independently of a UNIQUE constraint and is primarily used to speed up queries rather than enforce data integrity.

• UNIQUE Key: A constraint that enforces uniqueness of data at the table level. While many DBMSs create a UNIQUE index to enforce the constraint, this is merely an implementation choice, and should not necessarily be considered a given across all DBMSs.
• UNIQUE Index: An index that ensures uniqueness of the data at the index level. This ensures the uniqueness of values and can be used to speed up queries.

Also, it’s possible to create indexes that index only part of the data in a table (such as filtered indexes or partial indexes).

Best Practices and Considerations

Here are some best practices and things to think about when considering the use of UNIQUE keys in a database:

• Nullability: Be mindful of how your DBMS handles NULL values in UNIQUE keys. If you’re working with a DBMS that allows multiple NULLs (like PostgreSQL), consider whether this behavior is desirable for your use case.
• Naming Conventions: Always give meaningful names to UNIQUE keys when defining them as constraints. This makes database maintenance easier and improves code readability. It’s always a good idea to establish a naming convention for your database, and to name all objects accordingly. For example, you might call a UNIQUE key UQ_Users_Email, where UQ is the prefix for a UNIQUE constraint, Users is the table name, and Email is the column name.
• Composite Keys: Use composite UNIQUE keys when the uniqueness of records depends on a combination of columns rather than a single column.
• Performance Considerations: Since most DBMSs create an index when a UNIQUE key is defined, this can improve read operations but might slow down insertions and updates because the database must check for duplicates.