SQL — Unique

What is UNIQUE?

A UNIQUE constraint ensures all values in a column (or combination of columns) are different. Unlike PRIMARY KEY, a UNIQUE column can have one NULL value (in most databases).

Basic Syntax

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

Named UNIQUE Constraint

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  CONSTRAINT uq_email UNIQUE (email),
  CONSTRAINT uq_username UNIQUE (username)
);

Composite UNIQUE

Ensure the combination of columns is unique:

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (student_id, course_id)
);

A student can enroll in multiple courses, and a course can have multiple students — but the same student can't enroll in the same course twice.

Adding UNIQUE to Existing Table

ALTER TABLE users ADD UNIQUE (email);

Dropping UNIQUE Constraint

-- MySQL
ALTER TABLE users DROP INDEX uq_email;

-- PostgreSQL
ALTER TABLE users DROP CONSTRAINT uq_email;

UNIQUE vs PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE
NULL values	Not allowed	One NULL allowed (usually)
Per table	Only one	Multiple allowed
Purpose	Row identifier	Prevent duplicates

UNIQUE with Multiple NULLs

Behavior varies by database:

• MySQL/PostgreSQL: Multiple NULLs allowed (NULL != NULL)
• SQL Server: Only one NULL allowed

Practical Example

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(200) NOT NULL,
  instructor_email VARCHAR(100) NOT NULL,
  UNIQUE (slug),
  UNIQUE (title, instructor_email)  -- same instructor can't teach same course twice
);

UNIQUE Index Performance

A UNIQUE constraint automatically creates an index, which speeds up lookups:

-- This query benefits from the UNIQUE index on email
SELECT * FROM users WHERE email = 'alice@example.com';

Best Practices

• Add UNIQUE to any column that should never have duplicates (email, username, slug)
• Use composite UNIQUE for multi-column uniqueness rules
• Name your constraints for easier management
• Remember that UNIQUE and PRIMARY KEY serve different purposes

Practice

1. Create a users table with unique email and username columns
2. Try inserting duplicate emails and observe the error
3. Create a composite UNIQUE constraint on a junction table
4. Add a UNIQUE constraint to an existing table