What Is a Unique Constraint in SQL?

To ensure the uniqueness of data in a database, we use the SQL UNIQUE constraint. In this article, we’ll discuss how, when, and why to implement it.

The idea of a unique constraint is not unique to SQL; it’s a familiar concept in real life, although we probably call it something else! For example, think of ID numbers. There can be only one social security number (SSN) per person and each one must be unique. By using a UNIQUE constraint on a column, we define that this column cannot store duplicate values.

In this article, we’ll first review the basics of a UNIQUE constraint. Next, we’ll try to violate that constraint by adding duplicate values to a column that’s declared unique. Then I’ll present some real-world uses of the UNIQUE constraint. We’ll discuss how to define the UNIQUE constraint during table creation, how to add it to an existing table, and how to remove it. In the last section, I’ll touch on multicolumn UNIQUE constraints.

If you are interested in learning about other SQL constraints, check out our course on SQL Constraints and the article What is a SQL Constraint?.

Let’s get started!

SQL UNIQUE Constraint Basics

The UNIQUE constraint is one of many SQL constraints that can be defined for a column. A constraint is a rule that makes a column fulfill a particular condition (i.e. it prevents the user from entering the wrong type of data). A UNIQUE constraint dictates that a column must not store duplicate values.

Let’s see an example using the Employees table.

Each employee must have a unique number; hence, the EmployeeNumber column implements the UNIQUE constraint. By doing so, we ensure that no employees in the same company have the same employee number.

Why Use a Unique Constraint?

By using a UNIQUE constraint on a particular column, we ensure that this column cannot contain two of the same values.

Let’s see an example using the Person table with the columns Name, Age, and SSN. The SSN column implements the UNIQUE constraint, i.e. it cannot contain any duplicate values.

Trying to Violate a Unique Constraint

You might be wondering what happens when we try to violate a UNIQUE constraint. We can do that by simply inserting two rows with the same value for a column implementing the UNIQUE constraint.

Let’s look at the BankAccounts table. It assigns an account number to a person; one person can own one or more bank accounts, but one bank account cannot belong to more than one person.

The AccountNumber column implements a UNIQUE constraint. To try violating this constraint, we’ll insert another row into the BankAccounts table with an AccountNumber column value (8765234545677890123465) that’s already in the table:

INSERT INTO BankAccounts VALUES ('8765234545677890123465', '233');

After the execution of the above statement, we get an error message:

Error Code: 1062. Duplicate entry '8765234545677890123465' for key bankaccounts.AccountNumber'.

Constraints are like security guards – they make sure that all the non-compliant rows do not get inserted into the table.

When to Use a Unique Constraint (with Examples)

Example 1: Unique Values

We use a UNIQUE constraint when the column must contain a unique value for each row.

Let’s consider the Person table. Each person has a name, age, and a date of birth. However, these values are not unique to each person; there can be two persons with the same name or the same date of birth. However, a social security number value must be unique to each person.

Nowadays, you could also think of email addresses or phone numbers as being unique to each person, but this depends on the exact business requirements for your application.

Example 2: Row Identification

Another example of when to use a UNIQUE constraint is the unique identification of each row. That is usually ensured by the PRIMARY KEY constraint, which is made up of the UNIQUE and NOT NULL constraints.

The UNIQUE constraint is used to enforce the uniqueness of values in a column or in a set of columns in a particular table – that is, for columns that are not included in the table’s primary key set. The difference between PRIMARY KEY and UNIQUE is that UNIQUE allows NULL values, while PRIMARY KEY does not. The UNIQUE constraint considers a NULL value to be a regular value; hence, it cannot be duplicated, just like other regular values in a UNIQUE column.

A good example here is the Orders table. It uniquely identifies each row by keeping the values of the OrderId column unique and not null (by using the PRIMARY KEY constraint). It also enforces the uniqueness of order numbers by implementing the UNIQUE constraint for the OrderNumber column, as shown below:

This means that you could potentially have an order number being NULL for only one row – that would not be permitted when using the PRIMARY KEY constraint.

Let’s consider another example using the Flights table. Each row (i.e. each flight) must have its own unique Id number:

Here, we use the Id column as a primary key so we can, for example, sort all the flights by their times. The FlightNumber column ensures that each flight is uniquely identified using a special encoding that could include extra information (e.g. a code for each airline).

The UNIQUE constraint can also be used on multiple columns, i.e. using unique combinations of all columns’ values as a key. This will be discussed in the last section of this article.

Defining a UNIQUE Constraint

Now that we know what a UNIQUE constraint is and how to use it, let’s review various ways of defining a UNIQUE constraint.

Creating a Table

Defining a column as UNIQUE during table creation is very straightforward. Here’s an example:

CREATE TABLE ProductStorage (
ProductName VARCHAR(50) UNIQUE,
Quantity INTEGER
);

The ProductStorage table contains two columns:

• The ProductName is UNIQUE; it cannot contain any duplicate values.
• The Quantity column can store any INTEGER value.

Let’s insert some rows into the ProductStorage table.

INSERT INTO ProductStorage
VALUES ('Apple', 50),
('Banana', 40),
('Pear', 45);

INSERT INTO ProductStorage
VALUES ('Apple', 10);

Assume that the statements above are executed in the order they’re listed. Which of the statements, if any, will fail?

There is also another way to make sure that a column stores only unique values. The PRIMARY KEY constraint ensures that the column only stores unique, non-NULL values. Here’s how to implement a primary key:

CREATE TABLE ProductStorage (
ProductName VARCHAR(50) PRIMARY KEY,
Quantity INTEGER
);

Have you heard about the PRIMARY KEY feature in SQL? If not, check out our article What is a Primary Key in SQL?.

There’s more to using UNIQUE constraints. To use SQL constraints efficiently, you need to know the database structure. And you should understand what constraints are available in SQL so you can decide which one will best suit the information going into that column. Our track on Creating Database Structure can help you build the necessary knowledge.

Adding to an Existing Table

Sometimes, we may not know that a column needs to comply with the UNIQUE constraint at table creation. However, we can add a constraint to an existing table.

Let’s create an Orders table that contains the OrderNumber and CustomerId columns.

CREATE TABLE Orders (
OrderNumber INTEGER,
CustomerId INTEGER
);

As the Orders table does not implement any constraints, we can insert a row with no restrictions. Notice the values below:

INSERT INTO Orders
VALUES (23, 111),
(24, 222),
(24, 333);

At some point, we find that the OrderNumber column should store only unique values. Let’s try to implement the UNIQUE constraint on the OrderNumber column.

ALTER TABLE Orders ADD UNIQUE (OrderNumber);

But wait – we get an error. The error message says:

Error Code: 1062. Duplicate entry '24' for key 'orders.OrderNumber'. 

This is because the OrderNumber column already contains duplicate values.

If we remove the last inserted row with the values (24, 333), we will then be able to run this command successfully:

ALTER TABLE Orders ADD UNIQUE (OrderNumber);

Now OrderNumber will only accept unique values.

We could also implement the PRIMARY KEY constraint, as shown below:

ALTER TABLE Orders ADD PRIMARY KEY (OrderNumber);

And, so long as there are no duplicate values in the column, it’s as simple as that. For more examples, see our cookbook entry How to Make a Column Unique in SQL.

Remove a UNIQUE Constraint

You can remove A UNIQUE constraint from a table at any time. The process is similar to adding a constraint to an existing table:

ALTER TABLE Orders DROP CONSTRAINT OrderNumber;

In this case, the default name of the constraint is equivalent to the column name. Thus, after the DROP CONSTRAINT clause, we enter OrderNumber to drop the UNIQUE constraint implemented on this column.

We also provide a cookbook on How to Remove a Unique Constraint in SQL. Be sure to check out some of the examples there.

A Multicolumn Unique Constraint

You could also declare a UNIQUE constraint on more than one column. This ensures that the combination of these columns must be unique.

Let’s consider the Customers table, which contains the names, email addresses, and ages of each customer. To ensure that there is only one record per person, we could create a multicolumn UNIQUE constraint on the Name and Email columns.

By implementing a multicolumn UNIQUE constraint on the Name and Email columns, the database ensures that:

• The Name column can contain duplicate values.
• The Email column can contain duplicate values. Although most of us have our own email addresses, it’s possible that two people may share one email address.
• The combination of Name and Email values must be unique.

How do we declare a multicolumn UNIQUE constraint? Like this:

CREATE TABLE Customers (
Name VARCHAR(50),
Email VARCHAR(50),
Age INTEGER,
CONSTRAINT multicolumn_unique UNIQUE (Name, Email)
);

If we’d created the Customers table without any constraints, we could declare a constraint on the existing table:

ALTER TABLE Customers
ADD CONSTRAINT multicolumn_unique UNIQUE (Name, Email);

Analogously, we could drop this constraint, as shown below:

ALTER TABLE Customers
DROP CONSTRAINT multicolumn_unique;

The multicolumn UNIQUE constraint is a useful feature. To comply with database normal forms, we often must make columns atomic, i.e. containing a single value. For example, an address would be split into ‘street’, ‘house number’, ‘postcode’, and ‘city’. Having many columns containing single values, we can then ‘merge’ these columns to implementing the UNIQUE constraint.

SQL’s Useful UNIQUE Constraint

The UNIQUE constraint is very commonly used in SQL. It makes sure that a column or a combination of columns does not contain duplicate values. Its implementation is quite straightforward, and it’s very handy across a range of use cases. If you want to learn more about constraints, check out our Creating Database Structure track.