Unique Key

Introduction

You already know that a PRIMARY KEY guarantees every row has a distinct, non-NULL identifier. But what if you want to enforce uniqueness on a different column — like an email address or a username — without making it the primary key?

That is exactly what the UNIQUE constraint is for. This lesson covers how it works, how to define it, and how it compares to a primary key.

What is a Unique Key?

A UNIQUE constraint ensures that every value stored in a column (or combination of columns) is different from every other value in that column. MySQL rejects any INSERT or UPDATE that would create a duplicate.

Unlike a primary key, a unique column is allowed to contain NULL, and MySQL treats each NULL as unrelated to any other NULL — so a table can have multiple rows with a NULL email, but never two rows with the same non-NULL email.

Defining a Unique Key Inline

The simplest way to add a unique constraint is to write UNIQUE directly after a column definition, while creating the table.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

Now the "email" column can hold at most one row for each distinct email value.

Defining a Unique Key as a Table Constraint

You can also declare a unique constraint separately, as its own line in the CREATE TABLE statement. This form is required when the unique constraint spans more than one column.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT uq_students_email UNIQUE (email)
);

Giving the constraint an explicit name (uq_students_email) makes it easier to identify and drop later, for example with ALTER TABLE students DROP INDEX uq_students_email.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

Here, the same student can appear in many rows and the same course can appear in many rows, but the same student cannot be enrolled in the same course twice — the uniqueness applies to the combination of both columns.

Worked Example: Email Column

Let's see the constraint in action. First we insert a student with a unique email, which succeeds.

INSERT INTO students (id, name, email)
VALUES (1, 'Alex', 'alex@example.com');

SELECT * FROM students;

+----+------+-------------------+
| id | name | email             |
+----+------+-------------------+
| 1  | Alex | alex@example.com  |
+----+------+-------------------+

Now let's try to insert a second student who reuses that same email address.

INSERT INTO students (id, name, email)
VALUES (2, 'Sam', 'alex@example.com');

ERROR 1062 (23000): Duplicate entry 'alex@example.com' for key 'students.email'

MySQL blocks the insert immediately. Meanwhile, inserting NULL for email is allowed, and it does not conflict with any other row — even another NULL.

INSERT INTO students (id, name, email) VALUES (3, 'Priya', NULL);
INSERT INTO students (id, name, email) VALUES (4, 'Rohan', NULL);

SELECT * FROM students;

+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
| 1  | Alex  | alex@example.com  |
| 3  | Priya | NULL              |
| 4  | Rohan | NULL              |
+----+-------+-------------------+

Unique Key vs Primary Key

It is easy to confuse these two constraints since both prevent duplicates. Here is how they differ.

Multiple Unique Keys in One Table

A single table can enforce uniqueness on several independent columns at once.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    username VARCHAR(30) UNIQUE
);

Now both "email" and "username" are independently guaranteed to be unique — a duplicate in either column, on its own, is rejected.

Common Mistakes

Key Takeaways

Summary

The UNIQUE constraint is your tool for enforcing "no duplicates allowed" on any column that is not your primary key — emails, usernames, phone numbers, and similar business-critical fields.

In this lesson, you learned how to define unique keys, saw MySQL reject a duplicate insert, and compared unique keys against primary keys. Next, you'll learn how to give columns automatic fallback values using the DEFAULT constraint.