Unique Constraint

Definition

A `UNIQUE` constraint forbids duplicate values in a column (or set of columns), without the extra NOT NULL requirement that PRIMARY KEY bundles in. It is the SQL mechanism used to enforce alternate keys (5.4) and any other "must be distinct" business rule that isn't the table's main identifier.

How It Works

sql
CREATE TABLE employees (
  id INT PRIMARY KEY,
  email VARCHAR(150) UNIQUE,
  badge_number VARCHAR(20) UNIQUE
);

Both email and badge_number must each individually contain no duplicate values among non-NULL entries. A UNIQUE constraint can also span multiple columns, exactly like a composite key: UNIQUE (student_id, course_id, semester) enforces that the combination is distinct, not each column separately.

Finding violations of an intended-but-not-yet-declared UNIQUE constraint is a common real task:

sql
SELECT email, COUNT(*) AS cnt
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

This reveals every email value that appears more than once — exactly the rows that would block adding UNIQUE until cleaned up.

Edge Cases and Pitfalls

• As covered in 5.4: most engines treat multiple NULLs in a UNIQUE column as NOT duplicates of each other — NULL is never considered "equal" to another NULL, so several rows can all have NULL in a UNIQUE column simultaneously.
• Attempting to add a UNIQUE constraint to a column that already contains duplicate values fails immediately — the existing data must be cleaned up (using a query like the one above to find the offending rows) before the constraint can be applied.
• A UNIQUE constraint is checked on every INSERT and UPDATE, not just at table-creation time — this has a small but real performance cost on write-heavy tables, which is a genuine (if usually acceptable) trade-off against the correctness it buys.

Key Takeaways

• UNIQUE forbids duplicates but allows (typically multiple) NULLs, unlike PRIMARY KEY.
• GROUP BY <column(s)> HAVING COUNT(*) > 1 is the standard way to find existing duplicate values before adding the constraint.
• UNIQUE can be single-column or composite, just like a key.