UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are unique — no duplicate values are allowed.

It is used to enforce data integrity: Certain pieces of data (such as email addresses or usernames) should appear only once in a table.

Why Use UNIQUE?

• To prevent duplicate entries.
• To ensure certain fields are distinct.
• To enforce business rules (for example, each student must have a unique email).

Basic Syntax

When creating a table:

column_name data_type UNIQUE

Or as a table-level constraint:

UNIQUE (column1, column2, ...)

Example: Students with Unique Email

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

In this example:

• The email column has a UNIQUE constraint.
• You cannot insert two students with the same email.

Example: Attempting to Insert Duplicate

INSERT INTO students (name, email)
VALUES ('Alice Smith', 'alice@example.com');
 
INSERT INTO students (name, email)
VALUES ('Bob Johnson', 'alice@example.com');  -- This will cause an error!

Error: Duplicate entry for email.

UNIQUE Across Multiple Columns (Composite UNIQUE)

You can also define a UNIQUE constraint on a combination of columns:

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

In this example:

• A student can enroll in a course only once.
• The same student_id and course_id combination cannot appear twice.

Key Differences: UNIQUE vs PRIMARY KEY

Summary

• UNIQUE ensures no duplicate values in a column (or columns).
• It helps enforce data integrity.
• You can use multiple UNIQUE constraints in one table.
• It complements the PRIMARY KEY, which is always UNIQUE itself.