UNIQUE Constraint

The UNIQUE constraint in SQL ensures that all values in a column (or a combination of columns) are distinct across the table. This means no two rows can have the same value in that column.

Unlike PRIMARY KEY, a UNIQUE constraint allows NULL values, but each non-null value must be unique.

---

Syntax

CREATE TABLE table_name (
  column_name datatype UNIQUE
);

To apply on multiple columns (composite unique):

CREATE TABLE table_name (
  col1 datatype,
  col2 datatype,
  UNIQUE (col1, col2)
);

Or modify an existing table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

---

Example

CREATE TABLE users (
  user_id INT,
  email VARCHAR(100) UNIQUE
);

INSERT INTO users (user_id, email) VALUES (1, '[email protected]');
INSERT INTO users (user_id, email) VALUES (2, '[email protected]');
INSERT INTO users (user_id, email) VALUES (3, '[email protected]'); -- ❌ Error!

The third insert fails because [email protected] already exists.

---

When to Use UNIQUE?

• When a column or combination of columns must have no duplicates

• Useful for:

  • Email addresses
  • Usernames
  • National ID numbers

---

Next, we’ll cover the PRIMARY KEY constraint, which uniquely identifies each record in a table.