Unique Constraint in SQL
Overview
A UNIQUE constraint ensures that all values stored in a specified column or combination of columns are distinct across all rows in a table. In relational database architecture, unique constraints enforce business rules (such as unique email addresses, government IDs, usernames, or transaction reference numbers) and prevent accidental duplicate insertion. Under the hood, RDBMS engines automatically back unique constraints with unique indexes to guarantee high-performance duplicate detection.
What You Will Learn
In this lesson, you will master:
- Constraint Definition: Defining single-column and composite multi-column unique constraints.
- Index Creation Under the Hood: How database engines automatically generate B-Tree unique indexes.
- NULL Value Handling: The critical differences in how PostgreSQL, MySQL, and SQL Server handle multiple
NULLvalues in unique columns. - UNIQUE vs PRIMARY KEY: Architectural trade-offs between primary identifiers and candidate business keys.
- Adding & Dropping Constraints: Modifying existing tables safely without blocking production traffic.
Core Syntax & Table Definition
1. Single-Column Unique Constraint
You can define a unique constraint inline at the column level or out-of-line at the table level:
-- Inline Column Definition
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Explicitly Named Table-Level Constraint (Recommended Practice)
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
CONSTRAINT uq_users_email UNIQUE (email),
CONSTRAINT uq_users_username UNIQUE (username)
);
Best Practice: Always Name Constraints
Always name your constraints explicitly (e.g., CONSTRAINT uq_users_email UNIQUE (email)). If you omit the constraint name, the database generates a random internal identifier (e.g., sys_c001239), making future migrations, foreign key references, and debugging difficult.
Composite Multi-Column Unique Constraints
When uniqueness is required across a combination of multiple columns rather than a single column:
-- Scenario: A user can belong to multiple teams, but cannot join the same team twice
CREATE TABLE team_memberships (
membership_id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
team_id INT NOT NULL,
role VARCHAR(50) DEFAULT 'member',
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT uq_team_user UNIQUE (user_id, team_id)
);
-- Insertion Test
INSERT INTO team_memberships (user_id, team_id) VALUES (10, 101); -- Success
INSERT INTO team_memberships (user_id, team_id) VALUES (10, 102); -- Success (Different team)
INSERT INTO team_memberships (user_id, team_id) VALUES (10, 101); -- ERROR: duplicate key value violates unique constraint "uq_team_user"
The NULL Value Behavior Dilemma Across Databases
The ANSI SQL standard dictates that NULL represents an unknown value, meaning NULL = NULL evaluates to UNKNOWN (falsy). Consequently, database engines differ in how they treat NULL within unique columns:
| Database Engine | Multiple NULL Values Permitted in a UNIQUE Column? | Behavior Note |
| :--- | :--- | :--- |
| PostgreSQL | Yes (Default) | In PostgreSQL 15+, you can specify UNIQUE NULLS NOT DISTINCT to enforce single-NULL uniqueness. |
| MySQL (InnoDB) | Yes | Multiple NULLs are allowed in columns with a unique index. |
| SQL Server | No (By Default) | SQL Server treats NULL as a single distinct value, allowing only one NULL row. Filtered indexes (WHERE col IS NOT NULL) are required for multi-NULL support. |
| Oracle | Yes (Single column) | Allows multiple NULLs for single columns, but composite unique constraints treat all-NULL rows as unique. |
-- PostgreSQL 15+ syntax to disallow multiple NULLs:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
tax_id VARCHAR(50),
CONSTRAINT uq_accounts_tax_id UNIQUE NULLS NOT DISTINCT (tax_id)
);
UNIQUE vs PRIMARY KEY: Key Differences
| Feature | PRIMARY KEY | UNIQUE Constraint |
| :--- | :--- | :--- |
| Purpose | Uniquely identifies each physical row in the table | Enforces business rule uniqueness on candidate keys |
| Quantity per Table | Exactly one per table | Multiple allowed per table |
| NULL Acceptance | Strictly NOT NULL | Allows NULL (depending on RDBMS dialect) |
| Default Indexing | Usually creates a Clustered Index | Usually creates a Non-Clustered Index |
Modifying Existing Production Tables
-- Add Unique Constraint to existing table
ALTER TABLE users
ADD CONSTRAINT uq_users_phone UNIQUE (phone_number);
-- Note: This fails if duplicate phone numbers already exist in the table!
-- Drop Constraint
ALTER TABLE users
DROP CONSTRAINT uq_users_phone;
Common Mistakes & Gotchas
- Adding UNIQUE on Unindexed Existing Dirty Data: Attempting
ALTER TABLE ... ADD CONSTRAINT ... UNIQUEon a production table containing pre-existing duplicates fails immediately. You must run a deduplication query first. - Ignoring Write Amplification: Every unique constraint creates an underlying B-Tree index. Adding 5 unique constraints to a table means every
INSERTstatement incurs 5 additional index updates and validation checks.
Interview Perspective
SQL Interview Question: Can a UNIQUE constraint accept NULL values, and how does SQL Server differ from PostgreSQL?
Key Talking Points for Candidates:
- ANSI SQL Standard: Per ANSI standard, two
NULLs are not considered equal (NULL != NULL), so standard databases (PostgreSQL, MySQL, Oracle) allow multiple rows withNULLin a unique column. - SQL Server Exception: In Microsoft SQL Server, a standard
UNIQUEconstraint permits only a singleNULLvalue; inserting a secondNULLviolates uniqueness. To permit multiple NULLs in SQL Server, developers must create a Unique Filtered Index:CREATE UNIQUE NONCLUSTERED INDEX ix_uq ON t(col) WHERE col IS NOT NULL. - Unique Index Backing: All relational engines implement unique constraints using unique B-Tree indexes under the hood.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.