In MySQL, a UNIQUE constraint is a constraint type that ensures that all values in a column or a group of columns are distinct from each other. In other words, all values that go into the column or group of columns must be unique.

UNIQUE constraints can be applied whenever we want to prevent duplicate entries in specific columns without making them a primary key.

We can create UNIQUE constraints either at the time we create the table, or we can add it later.

Let’s take a look at both options.

Defining a UNIQUE Constraint During Table Creation

We can define a UNIQUE constraint while creating a table using the UNIQUE keyword next to the column name or in a separate UNIQUE clause.

Here’s an example of using the UNIQUE keyword next to the column name:

CREATE TABLE employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE,
    FullName VARCHAR(255)
);

In this example, the Email column has a UNIQUE constraint, ensuring no two employees can have the same email address.

An alternative is to provide a separate UNIQUE clause after all columns have been defined:

CREATE TABLE employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(255),
    FullName VARCHAR(255),
    -- Define UNIQUE constraint separately
    UNIQUE (Email)
);

Either way, the result is the same; both create a UNIQUE constraint for the Email column.

Populating the Table

The following rows will be inserted without any problems, because they contain distinct values for the Email column, and the table doesn’t contain any data yet:

INSERT INTO employees (Email, FullName) 
VALUES 
    ('[email protected]', 'Blake Baxter'),
    ('[email protected]', 'Jess Scrawley');

Output:

Commands completed successfully

These rows were inserted successfully.

However, the following code will cause an error, because the email is not unique:

INSERT INTO employees (Email, FullName) VALUES ('[email protected]', 'Bruce Baxter');

Output:

1062 (23000): Duplicate entry '[email protected]' for key 'employees.Email'

Our UNIQUE constraint worked as expected – it prevented duplicate values to enter the Email column, thus maintaining data integrity.

Adding a UNIQUE Constraint After Table Creation

If a table already exists, we can add a UNIQUE constraint using the ALTER TABLE statement.

Example:

CREATE TABLE products (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(255),
    SKU VARCHAR(50)
);

-- Add a UNIQUE constraint to the SKU column
ALTER TABLE products ADD CONSTRAINT unique_sku UNIQUE (SKU);

Here, the SKU column is altered to ensure that each product has a unique stock-keeping unit (SKU).

Populating the Table

First, let’s insert valid (unique) data:

INSERT INTO products (ProductName, SKU) 
VALUES 
    ('Laptop', 'SKU123'),
    ('Tablet', 'SKU456');

Output:

Commands completed successfully

Inserted successfully.

Now let’s try to insert a duplicate SKU:

INSERT INTO products (ProductName, SKU) VALUES ('Monitor', 'SKU123');

Output:

1062 (23000): Duplicate entry 'SKU123' for key 'products.unique_sku'

MySQL returned an error as expected, thanks to the UNIQUE constraint.

Composite UNIQUE Constraints

MySQL allows us to apply UNIQUE constraints to multiple columns. When applied to multiple columns, the constraint ensures that the values are unique across all columns, even though each individual column could potentially have duplicate values.

Example:

CREATE TABLE orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    UNIQUE (CustomerID, ProductID)
);

This constraint ensures that a customer can only order a particular product once, but they can order different products.

Populating the Table

INSERT INTO orders (CustomerID, ProductID) 
VALUES 
    (1, 101), -- Customer 1 orders Product 101
    (2, 102), -- Customer 2 orders Product 102
    (1, 102); -- Customer 1 orders Product 102

Output:

Commands completed successfully

Inserted successfully.

Now let’s try to insert a duplicate:

INSERT INTO orders (CustomerID, ProductID) VALUES (1, 101);

Output:

1062 (23000): Duplicate entry '1-101' for key 'orders.CustomerID'

Our composite UNIQUE constraint prevented the INSERT operation.

UNIQUE Index vs. UNIQUE Constraint

In MySQL, a UNIQUE constraint automatically creates a UNIQUE index behind the scenes in order to enforce the uniqueness. However, we can also explicitly create a UNIQUE index, which functions similarly.

We can create a UNIQUE index by using the CREATE UNIQUE INDEX statement:

CREATE TABLE users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50)
);

-- Create a unique index
CREATE UNIQUE INDEX idx_unique_username ON users (Username);

This is functionally the same as applying a UNIQUE constraint on the Username column, but the SQL syntax is slightly different. Both approaches prevent duplicate values.

Let’s insert some valid (unique) data:

INSERT INTO users (UserID, Username) 
VALUES 
    (1, 'blake69'),
    (2, 'jess123');

Output:

Commands completed successfully

And now for duplicate data:

INSERT INTO users (UserID, Username) VALUES (3, 'blake69');

Output:

1062 (23000): Duplicate entry 'blake69' for key 'users.idx_unique_username'

It successfully prevented the duplicate data from being inserted.

Summary

Here’s a quick summary of the above:

• We can define UNIQUE constraints during table creation.
• We can add UNIQUE constraints to an existing table using ALTER TABLE.
• We can apply UNIQUE constraints across multiple columns to enforce uniqueness on combinations of values.
• Creating a UNIQUE constraint also implicitly creates a UNIQUE index. But we can also create a UNIQUE index on its own if required.