SQL UNIQUE constraint

SQL Tutorial

Here is a clear, practical, and complete guide to the SQL UNIQUE constraint, including syntax, examples, engine differences, and best practices.

 What the SQL UNIQUE constraint Does

A UNIQUE constraint ensures that all values in a column (or set of columns) are different.

  • Prevents duplicate data

  • Enforces data integrity

  • Often used on: email, username, phone number, SKU, etc.

  • A table can have multiple UNIQUE constraints, unlike PRIMARY KEY

 Basic Syntax (CREATE TABLE)

Both email and username must be unique.

 Named UNIQUE Constraint

Good for maintenance & clarity:

UNIQUE Across Multiple Columns (Composite Unique)

Prevents duplicates across the combination, not individually.

  •  Allows same product for different customers
  •  Prevents the same customer from ordering the same product twice (useful in many-to-many tables)

ADD UNIQUE Constraint (ALTER TABLE)

PostgreSQL / SQL Server:

MySQL:

DROP UNIQUE Constraint

PostgreSQL:

SQL Server:

MySQL:

(UNIQUE is stored as an index)

 UNIQUE vs PRIMARY KEY

FeatureUNIQUEPRIMARY KEY
Requires uniqueness Yes Yes
Allows multiple per tableYes No (only 1 PK)
Allows NULL Yes* No
Implies NOT NULL NoYes
Index created automaticallyYes Yes

* MySQL treats UNIQUE column with multiple NULLs as allowed (special case).
Most DBs treat NULLs as non-equal → multiple NULLs allowed.

 Behavior Differences Across SQL Engines

 PostgreSQL

  • Allows multiple NULLs in UNIQUE columns

  • Uses explicit constraint names

 MySQL

  • UNIQUE = an indexed constraint

  • Multiple NULLs allowed

  • Dropped via DROP INDEX

 SQL Server

  • UNIQUE constraints or UNIQUE indexes

  • Multiple NULLs allowed

 Oracle

  • Similar to PostgreSQL

  • Multiple NULLs allowed

 Real-World Examples

1. Unique Email and Username

2. Prevent duplicate rows in a many-to-many table

3. Enforce unique SKU in products table

Best Practices

  •  Always name constraints (uq_users_email) – cleaner maintenance
  • Use composite UNIQUE on junction tables
  • Avoid storing different formats of same value (normalize emails)
  •  Indexes created by UNIQUE help performance on queries
  •  Avoid UNIQUE on frequently updated columns when possible
  •  Validate data before insert to avoid runtime constraint errors

You may also like...