Database Management SystemSQL Fundamentals

Constraints in DDL

LevelIntermediate

Duration90 mins

TopicSQL Fundamentals

3 / 5

UNIQUE Constraint

Beyond the Primary Key

While the PRIMARY KEY provides the principal identity for each row, many business scenarios demand uniqueness across other columns. An email address should be unique across all users. A product SKU should be unique across all products. A combination of (department, employee_number) might need to be unique even when neither is the primary key.

The UNIQUE constraint addresses these requirements, allowing you to enforce that no two rows have the same value (or combination of values) in specified columns—independent of the primary key.

Understanding UNIQUE constraints deeply reveals how relational databases model real-world uniqueness requirements that go beyond simple row identification.

What You Will Master

By the end of this page, you will understand how UNIQUE constraints work, how they differ from PRIMARY KEY, the nuanced behavior with NULL values, how to combine uniqueness with partial conditions, and how to design schemas that properly express business uniqueness rules.

UNIQUE vs PRIMARY KEY

Both PRIMARY KEY and UNIQUE constraints enforce uniqueness, but they differ in several important ways. Understanding these differences is crucial for proper schema design.

Fundamental Differences:

PRIMARY KEY vs UNIQUE Constraint Comparison
Characteristic	PRIMARY KEY	UNIQUE
Uniqueness	✓ Enforced	✓ Enforced
NULL Values	✗ NOT NULL (implicit)	✓ Allows NULL (by default)
Number Per Table	Exactly 1	Multiple allowed
Purpose	Row identity	Business rule enforcement
Foreign Key Target	Primary reference target	Can also be referenced (alternate key)
Index Type (SQL Server)	Clustered (default)	Non-clustered
Index Type (MySQL/InnoDB)	Clustered	Secondary (includes PK)
Conceptual Role	Principal identifier	Candidate key / Business constraint

The Candidate Key Connection:

In relational theory, a candidate key is any minimal set of columns that uniquely identifies each row. A table may have multiple candidate keys:

One is designated as the primary key
Others become alternate keys, typically implemented as UNIQUE constraints

For example, in a Users table:

user_id (surrogate) → PRIMARY KEY
email (natural) → UNIQUE constraint (alternate key)
username (natural) → UNIQUE constraint (alternate key)

All three uniquely identify a user, but only one is the primary key. The others remain enforceable via UNIQUE.

alternate_keys_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Multiple candidate keys: one primary, others as UNIQUE
 
CREATE TABLE users (
    user_id         INT NOT NULL,
    email           VARCHAR(255) NOT NULL,
    username        VARCHAR(50) NOT NULL,
    phone_number    VARCHAR(20),        -- Optional, but unique if provided
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Primary key: surrogate identifier
    CONSTRAINT pk_users 
        PRIMARY KEY (user_id),
    
    -- Alternate key 1: email (commonly used for login)
    CONSTRAINT uq_users_email 
        UNIQUE (email),
    
    -- Alternate key 2: username (display name / URL slug)
    CONSTRAINT uq_users_username 
        UNIQUE (username),
    
    -- Conditional uniqueness: phone unique if not null
    CONSTRAINT uq_users_phone 
        UNIQUE (phone_number)
);
 
-- All of these can uniquely identify a user:
SELECT * FROM users WHERE user_id = 42;
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT * FROM users WHERE username = 'alice';

Choosing What to Make UNIQUE

Apply UNIQUE constraints to any column (or column combination) that must be unique per business rules. Ask: 'Should two rows ever have the same value here?' If no, add a UNIQUE constraint. Common candidates: emails, usernames, SSNs, product codes, invoice numbers, natural identifiers.

UNIQUE and NULL Values

The handling of NULL values in UNIQUE constraints is one of the most nuanced aspects of SQL, and it varies between database systems. Understanding this behavior is critical for designing correct schemas.

The Core Question:

If a UNIQUE column allows NULL, can multiple rows have NULL values? The answer depends on your database:

Standard SQL Behavior:

In SQL standard and most databases (PostgreSQL, Oracle, SQLite), NULL ≠ NULL. Since NULL represents 'unknown', two unknown values are not considered equal. Therefore, multiple NULL values are permitted in a UNIQUE column.

SQL Server/MySQL Exception:

SQL Server and MySQL treat NULL as a single distinct value for UNIQUE purposes, so only one NULL is allowed per UNIQUE column (unless using filtered indexes).

NULL Handling in UNIQUE Constraints by Database
Database	Multiple NULLs Allowed?	Workaround for Different Behavior
PostgreSQL	Yes (standard)	Use UNIQUE + CHECK for single NULL
Oracle	Yes (standard)	NULLs never stored in indexes; all NULLs ignored
SQLite	Yes (standard)	—
SQL Server	No (single NULL)	Filtered unique index (WHERE col IS NOT NULL)
MySQL	No (single NULL)	Cannot have filtered indexes; use application logic

unique_null_behavior.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- PostgreSQL: Multiple NULLs allowed (standard behavior)
 
CREATE TABLE employees_pg (
    employee_id     SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,  -- Required, must be unique
    personal_email  VARCHAR(255) UNIQUE            -- Optional, unique if provided
);
 
-- These inserts ALL succeed in PostgreSQL:
INSERT INTO employees_pg (email, personal_email) VALUES ('a@work.com', 'a@home.com');
INSERT INTO employees_pg (email, personal_email) VALUES ('b@work.com', NULL);
INSERT INTO employees_pg (email, personal_email) VALUES ('c@work.com', NULL);  -- OK: multiple NULLs
 
 
 
-- SQL Server: Only one NULL allowed (non-standard)
-- To allow multiple NULLs, use a filtered index:
 
CREATE TABLE employees_mssql (
    employee_id     INT IDENTITY PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,
    personal_email  VARCHAR(255)
);
 
-- Filtered unique index: only validates non-NULL values
CREATE UNIQUE INDEX uq_employees_personal_email 
ON employees_mssql(personal_email)
WHERE personal_email IS NOT NULL;
 
-- Now multiple NULLs are allowed, but non-NULL values must be unique
 
 
 
-- PostgreSQL: Enforce "at most one NULL" if needed
-- (Opposite of default behavior)
 
CREATE TABLE single_null_example (
    id              SERIAL PRIMARY KEY,
    current_holder  VARCHAR(100) UNIQUE,
    
    -- This constraint allows at most one NULL (using exclusion constraint)
    CONSTRAINT check_single_null 
        EXCLUDE USING btree (
            (CASE WHEN current_holder IS NULL THEN 1 ELSE NULL END) WITH =
        ) WHERE (current_holder IS NULL)
);

NULL Behavior Is Database-Specific

Always test UNIQUE + NULL behavior in your specific database version. Migrating between databases can expose different NULL handling, causing unexpected constraint violations or duplicate data. Document your assumptions about NULL uniqueness in schema comments.

Syntax and Declaration

Like other constraints, UNIQUE can be declared at the column level or table level. The choice depends on whether you're constraining a single column or multiple columns together.

Column-Level UNIQUE:

column_level_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Column-level UNIQUE declaration
 
CREATE TABLE products (
    product_id      INT PRIMARY KEY,
    sku             VARCHAR(50) NOT NULL UNIQUE,    -- Single column, inline
    product_name    VARCHAR(200) NOT NULL,
    barcode         VARCHAR(20) UNIQUE,             -- Nullable but unique
    price           DECIMAL(10,2) NOT NULL
);
 
-- With explicit constraint naming
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_code VARCHAR(10) NOT NULL CONSTRAINT uq_dept_code UNIQUE,
    department_name VARCHAR(100) NOT NULL CONSTRAINT uq_dept_name UNIQUE
);

Table-Level UNIQUE (Required for Composite Uniqueness):

When multiple columns together must be unique, table-level declaration is required:

table_level_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Table-level UNIQUE for composite constraints
 
-- Scenario: Employee numbers are unique within each department
CREATE TABLE employees (
    employee_id     INT NOT NULL,
    department_id   INT NOT NULL,
    employee_number VARCHAR(10) NOT NULL,   -- Unique per department
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(100) NOT NULL,
    
    CONSTRAINT pk_employees 
        PRIMARY KEY (employee_id),
    
    -- Composite UNIQUE: employee_number unique within department
    CONSTRAINT uq_employee_dept_number 
        UNIQUE (department_id, employee_number),
    
    -- Single-column UNIQUE at table level
    CONSTRAINT uq_employee_email 
        UNIQUE (email)
);
 
-- These inserts succeed (different departments):
INSERT INTO employees VALUES (1, 10, 'E001', 'Alice', 'Smith', 'alice@co.com');
INSERT INTO employees VALUES (2, 20, 'E001', 'Bob', 'Jones', 'bob@co.com');  -- OK: dept 20
 
-- This fails (same department, same employee_number):
INSERT INTO employees VALUES (3, 10, 'E001', 'Carol', 'Williams', 'carol@co.com');
-- Error: duplicate key violates unique constraint "uq_employee_dept_number"
 
 
 
-- Scenario: Multi-tenant application with tenant-scoped uniqueness
CREATE TABLE tenant_products (
    product_id      INT NOT NULL,
    tenant_id       INT NOT NULL,
    sku             VARCHAR(50) NOT NULL,
    product_name    VARCHAR(200) NOT NULL,
    
    CONSTRAINT pk_tenant_products 
        PRIMARY KEY (product_id),
    
    -- SKU unique within tenant, not globally
    CONSTRAINT uq_tenant_product_sku 
        UNIQUE (tenant_id, sku)
);

Adding and Modifying UNIQUE Constraints:

alter_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Adding UNIQUE constraint to existing table
 
-- Step 1: Check for existing duplicates
SELECT email, COUNT(*) as occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
 
-- Step 2: Resolve duplicates (merge records, delete, or update)
 
-- Step 3: Add the constraint
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);
 
 
-- Dropping a UNIQUE constraint
ALTER TABLE users
DROP CONSTRAINT uq_users_email;
 
-- Alternative (using index name in some databases)
DROP INDEX uq_users_email;  -- PostgreSQL if created as index
DROP INDEX uq_users_email ON users;  -- MySQL
 
 
-- Adding composite UNIQUE
ALTER TABLE employees
ADD CONSTRAINT uq_emp_dept_number 
    UNIQUE (department_id, employee_number);
 
 
-- View all UNIQUE constraints (PostgreSQL)
SELECT 
    tc.constraint_name,
    tc.table_name,
    string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) as columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'UNIQUE'
GROUP BY tc.constraint_name, tc.table_name;

Constraint Naming Convention

Use uq_tablename_column or uq_tablename_col1_col2 for UNIQUE constraint names. This pattern makes error messages immediately understandable: 'duplicate key violates unique constraint uq_users_email' clearly indicates which uniqueness was violated.

Partial (Filtered) UNIQUE Constraints

Sometimes business rules require uniqueness only under certain conditions. For example:

Active users must have unique emails, but deactivated users' emails can be reused
Published articles must have unique slugs, but drafts don't need unique slugs
Only one row should have is_default = TRUE per category

Partial (Filtered) Unique Indexes solve this by applying uniqueness only to rows matching a condition.

partial_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- PostgreSQL: Partial (Filtered) UNIQUE Indexes
 
-- Scenario 1: Email unique only for active users
CREATE TABLE users (
    user_id         SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    deactivated_at  TIMESTAMP
);
 
-- Unique email constraint applies only to active users
CREATE UNIQUE INDEX uq_users_active_email 
ON users(email) 
WHERE is_active = TRUE;
 
-- These both succeed:
INSERT INTO users (email, is_active) VALUES ('alice@co.com', TRUE);
INSERT INTO users (email, is_active) VALUES ('alice@co.com', FALSE);  -- OK: not active
 
-- This fails:
INSERT INTO users (email, is_active) VALUES ('alice@co.com', TRUE);
-- Error: duplicate key value violates unique constraint
 
 
 
-- Scenario 2: Only one default per category
CREATE TABLE category_settings (
    setting_id      SERIAL PRIMARY KEY,
    category_id     INT NOT NULL,
    setting_name    VARCHAR(100) NOT NULL,
    is_default      BOOLEAN NOT NULL DEFAULT FALSE
);
 
-- Only one is_default=true allowed per category
CREATE UNIQUE INDEX uq_category_single_default 
ON category_settings(category_id) 
WHERE is_default = TRUE;
 
 
 
-- Scenario 3: Published articles have unique slugs
CREATE TABLE articles (
    article_id      SERIAL PRIMARY KEY,
    slug            VARCHAR(200) NOT NULL,
    title           VARCHAR(500) NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
);
 
CREATE UNIQUE INDEX uq_articles_published_slug 
ON articles(slug) 
WHERE status = 'published';
 
-- Two drafts can have same slug
INSERT INTO articles (slug, title, status) VALUES ('intro', 'Introduction', 'draft');
INSERT INTO articles (slug, title, status) VALUES ('intro', 'Another Intro', 'draft');  -- OK
 
-- But publishing one blocks publishing another with same slug
UPDATE articles SET status = 'published' WHERE article_id = 1;  -- OK
UPDATE articles SET status = 'published' WHERE article_id = 2;  -- Fails: slug conflict

Database Support for Partial Indexes

Partial/filtered indexes are supported in PostgreSQL (WHERE clause), SQL Server 2008+ (WHERE clause), and SQLite. MySQL and Oracle do not natively support partial indexes; workarounds include computed columns or application logic.

pg_partial_unique.sql
1
2
3
4
5
6
7
8
9
-- PostgreSQL partial unique index syntax
CREATE UNIQUE INDEX index_name
ON table_name(column_name)
WHERE condition;
 
-- Example: Unique constraint on soft-deleted data
CREATE UNIQUE INDEX uq_products_active_sku
ON products(sku)
WHERE deleted_at IS NULL;

Use Cases and Patterns

UNIQUE constraints serve various purposes beyond simple duplicate prevention. Here are common patterns that appear in production systems:

Pattern 1: Alternate Login Identifiers

pattern_login_identifiers.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Multiple unique identifiers for user authentication
CREATE TABLE user_accounts (
    user_id         SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    username        VARCHAR(50) NOT NULL,
    phone_number    VARCHAR(20),          -- Optional phone login
    oauth_google_id VARCHAR(100),         -- Optional Google login
    oauth_github_id VARCHAR(100),         -- Optional GitHub login
    password_hash   VARCHAR(255),
    
    -- Each identifier must be unique (when present)
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_phone UNIQUE (phone_number),
    CONSTRAINT uq_users_google UNIQUE (oauth_google_id),
    CONSTRAINT uq_users_github UNIQUE (oauth_github_id)
);
 
-- Login can use any unique identifier:
-- SELECT * FROM user_accounts WHERE email = ? OR username = ? OR phone_number = ?;

Pattern 2: Slugs for URL-Friendly Identifiers

pattern_slugs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- URL-friendly unique identifiers
CREATE TABLE blog_posts (
    post_id         SERIAL PRIMARY KEY,
    title           VARCHAR(500) NOT NULL,
    slug            VARCHAR(200) NOT NULL,  -- URL-friendly version of title
    content         TEXT,
    published_at    TIMESTAMP,
    
    CONSTRAINT uq_posts_slug UNIQUE (slug)
);
 
-- Slug generation (application logic):
-- "My Blog Post Title" → "my-blog-post-title"
-- On conflict, append number: "my-blog-post-title-2"
 
-- URLs like /blog/my-blog-post-title resolve to the right post:
SELECT * FROM blog_posts WHERE slug = 'my-blog-post-title';
 
 
-- Multi-tenant variant: slug unique per organization
CREATE TABLE org_pages (
    page_id         SERIAL PRIMARY KEY,
    org_id          INT NOT NULL,
    slug            VARCHAR(200) NOT NULL,
    title           VARCHAR(500) NOT NULL,
    
    -- Same slug allowed in different orgs
    CONSTRAINT uq_org_page_slug UNIQUE (org_id, slug)
);
 
-- /acme/about and /globex/about are both valid

Pattern 3: Natural Business Keys

pattern_business_keys.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Business-assigned identifiers alongside surrogate PKs
CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,  -- Surrogate for FK relationships
    sku             VARCHAR(50) NOT NULL, -- Business identifier
    upc             VARCHAR(12),          -- Universal Product Code (if exists)
    asin            VARCHAR(10),          -- Amazon identifier (if listed)
    mpn             VARCHAR(100),         -- Manufacturer Part Number
    
    -- All business identifiers must be unique when present
    CONSTRAINT uq_products_sku UNIQUE (sku),
    CONSTRAINT uq_products_upc UNIQUE (upc),
    CONSTRAINT uq_products_asin UNIQUE (asin),
    CONSTRAINT uq_products_mpn UNIQUE (mpn)
);
 
-- Different systems query by different identifiers:
-- Warehouse: SELECT * FROM products WHERE sku = 'SKU-12345';
-- POS system: SELECT * FROM products WHERE upc = '012345678901';
-- Amazon integration: SELECT * FROM products WHERE asin = 'B08N5WRWNW';

Pattern 4: Single Active Record

pattern_single_active.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Ensure only one 'active' or 'current' record per group
-- PostgreSQL syntax with partial index
 
CREATE TABLE subscription_plans (
    plan_id         SERIAL PRIMARY KEY,
    user_id         INT NOT NULL REFERENCES users(user_id),
    plan_type       VARCHAR(50) NOT NULL,
    is_current      BOOLEAN NOT NULL DEFAULT FALSE,
    valid_from      DATE NOT NULL,
    valid_to        DATE,
    
    CONSTRAINT fk_plans_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);
 
-- Only one current plan per user
CREATE UNIQUE INDEX uq_user_current_plan 
ON subscription_plans(user_id) 
WHERE is_current = TRUE;
 
-- Activation process:
BEGIN;
-- Deactivate existing
UPDATE subscription_plans SET is_current = FALSE WHERE user_id = 42;
-- Activate new
UPDATE subscription_plans SET is_current = TRUE WHERE plan_id = 567;
COMMIT;

Think in Business Rules

When designing schemas, ask 'What duplicates would indicate an error or inconsistency?' Every answer is a candidate for a UNIQUE constraint. Common patterns: login identifiers, external system IDs, natural business keys, URL slugs, and 'single active' flags.

UNIQUE and Foreign Key References

Foreign keys can reference not only primary keys but also columns with UNIQUE constraints. This enables referencing via alternate keys—useful when the natural key is more meaningful for certain relationships.

Why Reference a UNIQUE Column Instead of PK?

Natural Key Relationships: When the referencing table works with business identifiers
External System Integration: When foreign systems use their own identifiers
Human-Readable References: When the FK value should be meaningful to humans
Avoiding Joins: When storing the natural key avoids needing to join for display purposes

fk_to_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Referencing a UNIQUE column instead of PRIMARY KEY
 
CREATE TABLE countries (
    country_id      SERIAL PRIMARY KEY,
    country_code    CHAR(2) NOT NULL,     -- ISO 3166-1 alpha-2
    country_name    VARCHAR(100) NOT NULL,
    
    CONSTRAINT uq_countries_code UNIQUE (country_code)
);
 
CREATE TABLE addresses (
    address_id      SERIAL PRIMARY KEY,
    street          VARCHAR(200) NOT NULL,
    city            VARCHAR(100) NOT NULL,
    country_code    CHAR(2) NOT NULL,     -- Store meaningful code, not opaque ID
    
    -- Reference UNIQUE column, not PK
    CONSTRAINT fk_addresses_country 
        FOREIGN KEY (country_code) 
        REFERENCES countries(country_code)
);
 
-- Insert addresses with human-readable country codes:
INSERT INTO addresses (street, city, country_code) 
VALUES ('123 Main St', 'New York', 'US');
 
-- No join needed for display:
SELECT street, city, country_code FROM addresses;
-- Returns: 123 Main St, New York, US  (meaningful without join!)
 
 
-- Another example: Currency references
CREATE TABLE currencies (
    currency_id     SERIAL PRIMARY KEY,
    currency_code   CHAR(3) NOT NULL,     -- ISO 4217: USD, EUR, GBP
    currency_name   VARCHAR(50) NOT NULL,
    
    CONSTRAINT uq_currencies_code UNIQUE (currency_code)
);
 
CREATE TABLE invoices (
    invoice_id      SERIAL PRIMARY KEY,
    amount          DECIMAL(15,2) NOT NULL,
    currency_code   CHAR(3) NOT NULL,     -- Store 'USD', not currency_id
    
    CONSTRAINT fk_invoices_currency 
        FOREIGN KEY (currency_code) 
        REFERENCES currencies(currency_code)
);

Trade-off: Storage vs Joins

Storing natural keys (like country codes) avoids joins for readability but uses more storage than integer IDs. For frequently displayed, rarely changing reference data (countries, currencies, status codes), the storage cost is negligible and the development simplicity is significant.

Performance Considerations

UNIQUE constraints have performance implications that affect both reads and writes:

Index Creation:

When you create a UNIQUE constraint, the database automatically creates a unique index on the column(s). This index:

Improves Read Performance: Queries filtering or joining on the unique column(s) benefit from the index
Consumes Storage: Index requires disk space proportional to table size × column size
Affects Write Performance: Inserts and updates must check and maintain the index

Write Overhead:

UNIQUE Constraint Write Impact
Operation	Without UNIQUE	With UNIQUE
INSERT	Write row only	Write row + check uniqueness + update index
UPDATE (on UQ column)	Update row only	Update row + check uniqueness + update index
DELETE	Remove row only	Remove row + update index
Bulk LOAD	Fast sequential writes	Per-row uniqueness checks (slower)

Index Size Considerations:

Unique indexes on wide columns (like VARCHAR(255) email addresses) consume significant space:

Index size ≈ Number of rows × (Column size + 8 bytes overhead)
1 million rows × 100 bytes average email ≈ 100 MB index size

Wider columns also mean slower comparisons and more memory pressure.

Best Practices for Performance:

UNIQUE Constraint Performance Tips

•Prefer Narrow Columns: Use CHAR(3) currency codes, not VARCHAR(255) currency names, for uniqueness enforcement.
•Consider Case Sensitivity: Unique indexes are case-sensitive by default. Use LOWER(column) or CITEXT (PostgreSQL) if case-insensitivity is needed. This affects index effectiveness.
•Batch Inserts Efficiently: For bulk loads, consider temporarily dropping UNIQUE constraints, loading data, removing duplicates, then re-adding constraints.
•Use Partial Indexes: When uniqueness applies only to a subset of rows, partial indexes exclude rows that don't need checking (PostgreSQL, SQL Server).
•Monitor Index Bloat: Unique indexes on frequently updated columns can become bloated. Schedule regular REINDEX operations (PostgreSQL) or index rebuilds.

unique_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Case-insensitive uniqueness (PostgreSQL)
 
-- Option 1: CITEXT type (case-insensitive text)
CREATE EXTENSION IF NOT EXISTS citext;
 
CREATE TABLE users_citext (
    user_id         SERIAL PRIMARY KEY,
    email           CITEXT NOT NULL UNIQUE,  -- alice@co.com = Alice@CO.COM
    username        CITEXT NOT NULL UNIQUE
);
 
 
-- Option 2: Functional index on LOWER()
CREATE TABLE users_lower (
    user_id         SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    username        VARCHAR(50) NOT NULL
);
 
CREATE UNIQUE INDEX uq_users_email_lower ON users_lower(LOWER(email));
CREATE UNIQUE INDEX uq_users_username_lower ON users_lower(LOWER(username));
 
-- Queries should also use LOWER() to use the index:
SELECT * FROM users_lower WHERE LOWER(email) = LOWER('Alice@Co.Com');
 
 
-- Bulk loading with constraint management
-- Step 1: Drop constraint
ALTER TABLE products DROP CONSTRAINT uq_products_sku;
 
-- Step 2: Bulk load data
COPY products FROM '/data/products.csv' CSV;
 
-- Step 3: Remove duplicates (keep first occurrence)
DELETE FROM products 
WHERE product_id NOT IN (
    SELECT MIN(product_id) 
    FROM products 
    GROUP BY sku
);
 
-- Step 4: Re-add constraint
ALTER TABLE products ADD CONSTRAINT uq_products_sku UNIQUE (sku);

Summary: UNIQUE Constraint Mastery

We've explored UNIQUE constraints comprehensively—from theoretical foundations through practical patterns to performance considerations. Let's consolidate the essential knowledge:

Key Takeaways

•UNIQUE ≠ PRIMARY KEY: UNIQUE allows NULLs (behavior varies by DBMS), multiple per table, and implements alternate/candidate keys.
•NULL Behavior Varies: PostgreSQL allows multiple NULLs; SQL Server/MySQL allow only one NULL. Test your target database.
•Column or Table Level: Single-column uniqueness can use inline syntax; composite uniqueness requires table-level declaration.
•Partial/Filtered Indexes: Enforce uniqueness only on rows matching a condition (active users only, published articles only). PostgreSQL and SQL Server support this.
•Can Be FK Target: Foreign keys can reference UNIQUE columns, enabling natural key relationships without joining for common lookups.
•Creates Index Automatically: UNIQUE constraints create indexes, benefiting reads but adding overhead to writes.
•Common Patterns: Login identifiers, URL slugs, business keys, single-active records, multi-tenant scoped uniqueness.
•Performance Aware: Wide unique columns create large indexes. Consider case sensitivity, partial indexes, and bulk load strategies.

What's Next:

With uniqueness enforcement covered through PRIMARY KEY and UNIQUE, we'll explore the simplest but most pervasive constraint: NOT NULL. The next page explains why preventing NULL values is fundamental to data integrity and how to think about nullable versus non-nullable columns.

Page Complete

You now have a comprehensive understanding of UNIQUE constraints—how they differ from primary keys, their nuanced NULL behavior, partial index patterns, and performance implications. This knowledge enables you to properly model business uniqueness requirements in your database schemas.

3 / 5

Loading learning content...

Database Management SystemSQL Fundamentals

Constraints in DDL

LevelIntermediate

Duration90 mins

TopicSQL Fundamentals

3 / 5

UNIQUE Constraint

Beyond the Primary Key

The UNIQUE constraint addresses these requirements, allowing you to enforce that no two rows have the same value (or combination of values) in specified columns—independent of the primary key.

Understanding UNIQUE constraints deeply reveals how relational databases model real-world uniqueness requirements that go beyond simple row identification.

What You Will Master

UNIQUE vs PRIMARY KEY

Both PRIMARY KEY and UNIQUE constraints enforce uniqueness, but they differ in several important ways. Understanding these differences is crucial for proper schema design.

Fundamental Differences:

PRIMARY KEY vs UNIQUE Constraint Comparison
Characteristic	PRIMARY KEY	UNIQUE
Uniqueness	✓ Enforced	✓ Enforced
NULL Values	✗ NOT NULL (implicit)	✓ Allows NULL (by default)
Number Per Table	Exactly 1	Multiple allowed
Purpose	Row identity	Business rule enforcement
Foreign Key Target	Primary reference target	Can also be referenced (alternate key)
Index Type (SQL Server)	Clustered (default)	Non-clustered
Index Type (MySQL/InnoDB)	Clustered	Secondary (includes PK)
Conceptual Role	Principal identifier	Candidate key / Business constraint

The Candidate Key Connection:

In relational theory, a candidate key is any minimal set of columns that uniquely identifies each row. A table may have multiple candidate keys:

One is designated as the primary key
Others become alternate keys, typically implemented as UNIQUE constraints

For example, in a Users table:

user_id (surrogate) → PRIMARY KEY
email (natural) → UNIQUE constraint (alternate key)
username (natural) → UNIQUE constraint (alternate key)

All three uniquely identify a user, but only one is the primary key. The others remain enforceable via UNIQUE.

alternate_keys_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Multiple candidate keys: one primary, others as UNIQUE
 
CREATE TABLE users (
    user_id         INT NOT NULL,
    email           VARCHAR(255) NOT NULL,
    username        VARCHAR(50) NOT NULL,
    phone_number    VARCHAR(20),        -- Optional, but unique if provided
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Primary key: surrogate identifier
    CONSTRAINT pk_users 
        PRIMARY KEY (user_id),
    
    -- Alternate key 1: email (commonly used for login)
    CONSTRAINT uq_users_email 
        UNIQUE (email),
    
    -- Alternate key 2: username (display name / URL slug)
    CONSTRAINT uq_users_username 
        UNIQUE (username),
    
    -- Conditional uniqueness: phone unique if not null
    CONSTRAINT uq_users_phone 
        UNIQUE (phone_number)
);
 
-- All of these can uniquely identify a user:
SELECT * FROM users WHERE user_id = 42;
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT * FROM users WHERE username = 'alice';

Choosing What to Make UNIQUE

UNIQUE and NULL Values

The Core Question:

If a UNIQUE column allows NULL, can multiple rows have NULL values? The answer depends on your database:

Standard SQL Behavior:

SQL Server/MySQL Exception:

SQL Server and MySQL treat NULL as a single distinct value for UNIQUE purposes, so only one NULL is allowed per UNIQUE column (unless using filtered indexes).

NULL Handling in UNIQUE Constraints by Database
Database	Multiple NULLs Allowed?	Workaround for Different Behavior
PostgreSQL	Yes (standard)	Use UNIQUE + CHECK for single NULL
Oracle	Yes (standard)	NULLs never stored in indexes; all NULLs ignored
SQLite	Yes (standard)	—
SQL Server	No (single NULL)	Filtered unique index (WHERE col IS NOT NULL)
MySQL	No (single NULL)	Cannot have filtered indexes; use application logic

unique_null_behavior.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- PostgreSQL: Multiple NULLs allowed (standard behavior)
 
CREATE TABLE employees_pg (
    employee_id     SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,  -- Required, must be unique
    personal_email  VARCHAR(255) UNIQUE            -- Optional, unique if provided
);
 
-- These inserts ALL succeed in PostgreSQL:
INSERT INTO employees_pg (email, personal_email) VALUES ('a@work.com', 'a@home.com');
INSERT INTO employees_pg (email, personal_email) VALUES ('b@work.com', NULL);
INSERT INTO employees_pg (email, personal_email) VALUES ('c@work.com', NULL);  -- OK: multiple NULLs
 
 
 
-- SQL Server: Only one NULL allowed (non-standard)
-- To allow multiple NULLs, use a filtered index:
 
CREATE TABLE employees_mssql (
    employee_id     INT IDENTITY PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,
    personal_email  VARCHAR(255)
);
 
-- Filtered unique index: only validates non-NULL values
CREATE UNIQUE INDEX uq_employees_personal_email 
ON employees_mssql(personal_email)
WHERE personal_email IS NOT NULL;
 
-- Now multiple NULLs are allowed, but non-NULL values must be unique
 
 
 
-- PostgreSQL: Enforce "at most one NULL" if needed
-- (Opposite of default behavior)
 
CREATE TABLE single_null_example (
    id              SERIAL PRIMARY KEY,
    current_holder  VARCHAR(100) UNIQUE,
    
    -- This constraint allows at most one NULL (using exclusion constraint)
    CONSTRAINT check_single_null 
        EXCLUDE USING btree (
            (CASE WHEN current_holder IS NULL THEN 1 ELSE NULL END) WITH =
        ) WHERE (current_holder IS NULL)
);

NULL Behavior Is Database-Specific

Syntax and Declaration

Like other constraints, UNIQUE can be declared at the column level or table level. The choice depends on whether you're constraining a single column or multiple columns together.

Column-Level UNIQUE:

column_level_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Column-level UNIQUE declaration
 
CREATE TABLE products (
    product_id      INT PRIMARY KEY,
    sku             VARCHAR(50) NOT NULL UNIQUE,    -- Single column, inline
    product_name    VARCHAR(200) NOT NULL,
    barcode         VARCHAR(20) UNIQUE,             -- Nullable but unique
    price           DECIMAL(10,2) NOT NULL
);
 
-- With explicit constraint naming
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_code VARCHAR(10) NOT NULL CONSTRAINT uq_dept_code UNIQUE,
    department_name VARCHAR(100) NOT NULL CONSTRAINT uq_dept_name UNIQUE
);

Table-Level UNIQUE (Required for Composite Uniqueness):

When multiple columns together must be unique, table-level declaration is required:

table_level_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Table-level UNIQUE for composite constraints
 
-- Scenario: Employee numbers are unique within each department
CREATE TABLE employees (
    employee_id     INT NOT NULL,
    department_id   INT NOT NULL,
    employee_number VARCHAR(10) NOT NULL,   -- Unique per department
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(100) NOT NULL,
    
    CONSTRAINT pk_employees 
        PRIMARY KEY (employee_id),
    
    -- Composite UNIQUE: employee_number unique within department
    CONSTRAINT uq_employee_dept_number 
        UNIQUE (department_id, employee_number),
    
    -- Single-column UNIQUE at table level
    CONSTRAINT uq_employee_email 
        UNIQUE (email)
);
 
-- These inserts succeed (different departments):
INSERT INTO employees VALUES (1, 10, 'E001', 'Alice', 'Smith', 'alice@co.com');
INSERT INTO employees VALUES (2, 20, 'E001', 'Bob', 'Jones', 'bob@co.com');  -- OK: dept 20
 
-- This fails (same department, same employee_number):
INSERT INTO employees VALUES (3, 10, 'E001', 'Carol', 'Williams', 'carol@co.com');
-- Error: duplicate key violates unique constraint "uq_employee_dept_number"
 
 
 
-- Scenario: Multi-tenant application with tenant-scoped uniqueness
CREATE TABLE tenant_products (
    product_id      INT NOT NULL,
    tenant_id       INT NOT NULL,
    sku             VARCHAR(50) NOT NULL,
    product_name    VARCHAR(200) NOT NULL,
    
    CONSTRAINT pk_tenant_products 
        PRIMARY KEY (product_id),
    
    -- SKU unique within tenant, not globally
    CONSTRAINT uq_tenant_product_sku 
        UNIQUE (tenant_id, sku)
);

Adding and Modifying UNIQUE Constraints:

alter_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Adding UNIQUE constraint to existing table
 
-- Step 1: Check for existing duplicates
SELECT email, COUNT(*) as occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
 
-- Step 2: Resolve duplicates (merge records, delete, or update)
 
-- Step 3: Add the constraint
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);
 
 
-- Dropping a UNIQUE constraint
ALTER TABLE users
DROP CONSTRAINT uq_users_email;
 
-- Alternative (using index name in some databases)
DROP INDEX uq_users_email;  -- PostgreSQL if created as index
DROP INDEX uq_users_email ON users;  -- MySQL
 
 
-- Adding composite UNIQUE
ALTER TABLE employees
ADD CONSTRAINT uq_emp_dept_number 
    UNIQUE (department_id, employee_number);
 
 
-- View all UNIQUE constraints (PostgreSQL)
SELECT 
    tc.constraint_name,
    tc.table_name,
    string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) as columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'UNIQUE'
GROUP BY tc.constraint_name, tc.table_name;

Constraint Naming Convention

Partial (Filtered) UNIQUE Constraints

Sometimes business rules require uniqueness only under certain conditions. For example:

Active users must have unique emails, but deactivated users' emails can be reused
Published articles must have unique slugs, but drafts don't need unique slugs
Only one row should have is_default = TRUE per category

Partial (Filtered) Unique Indexes solve this by applying uniqueness only to rows matching a condition.

partial_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- PostgreSQL: Partial (Filtered) UNIQUE Indexes
 
-- Scenario 1: Email unique only for active users
CREATE TABLE users (
    user_id         SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    deactivated_at  TIMESTAMP
);
 
-- Unique email constraint applies only to active users
CREATE UNIQUE INDEX uq_users_active_email 
ON users(email) 
WHERE is_active = TRUE;
 
-- These both succeed:
INSERT INTO users (email, is_active) VALUES ('alice@co.com', TRUE);
INSERT INTO users (email, is_active) VALUES ('alice@co.com', FALSE);  -- OK: not active
 
-- This fails:
INSERT INTO users (email, is_active) VALUES ('alice@co.com', TRUE);
-- Error: duplicate key value violates unique constraint
 
 
 
-- Scenario 2: Only one default per category
CREATE TABLE category_settings (
    setting_id      SERIAL PRIMARY KEY,
    category_id     INT NOT NULL,
    setting_name    VARCHAR(100) NOT NULL,
    is_default      BOOLEAN NOT NULL DEFAULT FALSE
);
 
-- Only one is_default=true allowed per category
CREATE UNIQUE INDEX uq_category_single_default 
ON category_settings(category_id) 
WHERE is_default = TRUE;
 
 
 
-- Scenario 3: Published articles have unique slugs
CREATE TABLE articles (
    article_id      SERIAL PRIMARY KEY,
    slug            VARCHAR(200) NOT NULL,
    title           VARCHAR(500) NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft'
);
 
CREATE UNIQUE INDEX uq_articles_published_slug 
ON articles(slug) 
WHERE status = 'published';
 
-- Two drafts can have same slug
INSERT INTO articles (slug, title, status) VALUES ('intro', 'Introduction', 'draft');
INSERT INTO articles (slug, title, status) VALUES ('intro', 'Another Intro', 'draft');  -- OK
 
-- But publishing one blocks publishing another with same slug
UPDATE articles SET status = 'published' WHERE article_id = 1;  -- OK
UPDATE articles SET status = 'published' WHERE article_id = 2;  -- Fails: slug conflict

Database Support for Partial Indexes

pg_partial_unique.sql
1
2
3
4
5
6
7
8
9
-- PostgreSQL partial unique index syntax
CREATE UNIQUE INDEX index_name
ON table_name(column_name)
WHERE condition;
 
-- Example: Unique constraint on soft-deleted data
CREATE UNIQUE INDEX uq_products_active_sku
ON products(sku)
WHERE deleted_at IS NULL;

Use Cases and Patterns

UNIQUE constraints serve various purposes beyond simple duplicate prevention. Here are common patterns that appear in production systems:

Pattern 1: Alternate Login Identifiers

pattern_login_identifiers.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Multiple unique identifiers for user authentication
CREATE TABLE user_accounts (
    user_id         SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    username        VARCHAR(50) NOT NULL,
    phone_number    VARCHAR(20),          -- Optional phone login
    oauth_google_id VARCHAR(100),         -- Optional Google login
    oauth_github_id VARCHAR(100),         -- Optional GitHub login
    password_hash   VARCHAR(255),
    
    -- Each identifier must be unique (when present)
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_phone UNIQUE (phone_number),
    CONSTRAINT uq_users_google UNIQUE (oauth_google_id),
    CONSTRAINT uq_users_github UNIQUE (oauth_github_id)
);
 
-- Login can use any unique identifier:
-- SELECT * FROM user_accounts WHERE email = ? OR username = ? OR phone_number = ?;

Pattern 2: Slugs for URL-Friendly Identifiers

pattern_slugs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- URL-friendly unique identifiers
CREATE TABLE blog_posts (
    post_id         SERIAL PRIMARY KEY,
    title           VARCHAR(500) NOT NULL,
    slug            VARCHAR(200) NOT NULL,  -- URL-friendly version of title
    content         TEXT,
    published_at    TIMESTAMP,
    
    CONSTRAINT uq_posts_slug UNIQUE (slug)
);
 
-- Slug generation (application logic):
-- "My Blog Post Title" → "my-blog-post-title"
-- On conflict, append number: "my-blog-post-title-2"
 
-- URLs like /blog/my-blog-post-title resolve to the right post:
SELECT * FROM blog_posts WHERE slug = 'my-blog-post-title';
 
 
-- Multi-tenant variant: slug unique per organization
CREATE TABLE org_pages (
    page_id         SERIAL PRIMARY KEY,
    org_id          INT NOT NULL,
    slug            VARCHAR(200) NOT NULL,
    title           VARCHAR(500) NOT NULL,
    
    -- Same slug allowed in different orgs
    CONSTRAINT uq_org_page_slug UNIQUE (org_id, slug)
);
 
-- /acme/about and /globex/about are both valid

Pattern 3: Natural Business Keys

pattern_business_keys.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Business-assigned identifiers alongside surrogate PKs
CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,  -- Surrogate for FK relationships
    sku             VARCHAR(50) NOT NULL, -- Business identifier
    upc             VARCHAR(12),          -- Universal Product Code (if exists)
    asin            VARCHAR(10),          -- Amazon identifier (if listed)
    mpn             VARCHAR(100),         -- Manufacturer Part Number
    
    -- All business identifiers must be unique when present
    CONSTRAINT uq_products_sku UNIQUE (sku),
    CONSTRAINT uq_products_upc UNIQUE (upc),
    CONSTRAINT uq_products_asin UNIQUE (asin),
    CONSTRAINT uq_products_mpn UNIQUE (mpn)
);
 
-- Different systems query by different identifiers:
-- Warehouse: SELECT * FROM products WHERE sku = 'SKU-12345';
-- POS system: SELECT * FROM products WHERE upc = '012345678901';
-- Amazon integration: SELECT * FROM products WHERE asin = 'B08N5WRWNW';

Pattern 4: Single Active Record

pattern_single_active.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Ensure only one 'active' or 'current' record per group
-- PostgreSQL syntax with partial index
 
CREATE TABLE subscription_plans (
    plan_id         SERIAL PRIMARY KEY,
    user_id         INT NOT NULL REFERENCES users(user_id),
    plan_type       VARCHAR(50) NOT NULL,
    is_current      BOOLEAN NOT NULL DEFAULT FALSE,
    valid_from      DATE NOT NULL,
    valid_to        DATE,
    
    CONSTRAINT fk_plans_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);
 
-- Only one current plan per user
CREATE UNIQUE INDEX uq_user_current_plan 
ON subscription_plans(user_id) 
WHERE is_current = TRUE;
 
-- Activation process:
BEGIN;
-- Deactivate existing
UPDATE subscription_plans SET is_current = FALSE WHERE user_id = 42;
-- Activate new
UPDATE subscription_plans SET is_current = TRUE WHERE plan_id = 567;
COMMIT;

Think in Business Rules

UNIQUE and Foreign Key References

Why Reference a UNIQUE Column Instead of PK?

Natural Key Relationships: When the referencing table works with business identifiers
External System Integration: When foreign systems use their own identifiers
Human-Readable References: When the FK value should be meaningful to humans
Avoiding Joins: When storing the natural key avoids needing to join for display purposes

fk_to_unique.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Referencing a UNIQUE column instead of PRIMARY KEY
 
CREATE TABLE countries (
    country_id      SERIAL PRIMARY KEY,
    country_code    CHAR(2) NOT NULL,     -- ISO 3166-1 alpha-2
    country_name    VARCHAR(100) NOT NULL,
    
    CONSTRAINT uq_countries_code UNIQUE (country_code)
);
 
CREATE TABLE addresses (
    address_id      SERIAL PRIMARY KEY,
    street          VARCHAR(200) NOT NULL,
    city            VARCHAR(100) NOT NULL,
    country_code    CHAR(2) NOT NULL,     -- Store meaningful code, not opaque ID
    
    -- Reference UNIQUE column, not PK
    CONSTRAINT fk_addresses_country 
        FOREIGN KEY (country_code) 
        REFERENCES countries(country_code)
);
 
-- Insert addresses with human-readable country codes:
INSERT INTO addresses (street, city, country_code) 
VALUES ('123 Main St', 'New York', 'US');
 
-- No join needed for display:
SELECT street, city, country_code FROM addresses;
-- Returns: 123 Main St, New York, US  (meaningful without join!)
 
 
-- Another example: Currency references
CREATE TABLE currencies (
    currency_id     SERIAL PRIMARY KEY,
    currency_code   CHAR(3) NOT NULL,     -- ISO 4217: USD, EUR, GBP
    currency_name   VARCHAR(50) NOT NULL,
    
    CONSTRAINT uq_currencies_code UNIQUE (currency_code)
);
 
CREATE TABLE invoices (
    invoice_id      SERIAL PRIMARY KEY,
    amount          DECIMAL(15,2) NOT NULL,
    currency_code   CHAR(3) NOT NULL,     -- Store 'USD', not currency_id
    
    CONSTRAINT fk_invoices_currency 
        FOREIGN KEY (currency_code) 
        REFERENCES currencies(currency_code)
);

Trade-off: Storage vs Joins

Performance Considerations

UNIQUE constraints have performance implications that affect both reads and writes:

Index Creation:

When you create a UNIQUE constraint, the database automatically creates a unique index on the column(s). This index:

Improves Read Performance: Queries filtering or joining on the unique column(s) benefit from the index
Consumes Storage: Index requires disk space proportional to table size × column size
Affects Write Performance: Inserts and updates must check and maintain the index

Write Overhead:

UNIQUE Constraint Write Impact
Operation	Without UNIQUE	With UNIQUE
INSERT	Write row only	Write row + check uniqueness + update index
UPDATE (on UQ column)	Update row only	Update row + check uniqueness + update index
DELETE	Remove row only	Remove row + update index
Bulk LOAD	Fast sequential writes	Per-row uniqueness checks (slower)

Index Size Considerations:

Unique indexes on wide columns (like VARCHAR(255) email addresses) consume significant space:

Index size ≈ Number of rows × (Column size + 8 bytes overhead)
1 million rows × 100 bytes average email ≈ 100 MB index size

Wider columns also mean slower comparisons and more memory pressure.

Best Practices for Performance:

UNIQUE Constraint Performance Tips

•Prefer Narrow Columns: Use CHAR(3) currency codes, not VARCHAR(255) currency names, for uniqueness enforcement.
•Consider Case Sensitivity: Unique indexes are case-sensitive by default. Use LOWER(column) or CITEXT (PostgreSQL) if case-insensitivity is needed. This affects index effectiveness.
•Batch Inserts Efficiently: For bulk loads, consider temporarily dropping UNIQUE constraints, loading data, removing duplicates, then re-adding constraints.
•Use Partial Indexes: When uniqueness applies only to a subset of rows, partial indexes exclude rows that don't need checking (PostgreSQL, SQL Server).
•Monitor Index Bloat: Unique indexes on frequently updated columns can become bloated. Schedule regular REINDEX operations (PostgreSQL) or index rebuilds.

unique_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Case-insensitive uniqueness (PostgreSQL)
 
-- Option 1: CITEXT type (case-insensitive text)
CREATE EXTENSION IF NOT EXISTS citext;
 
CREATE TABLE users_citext (
    user_id         SERIAL PRIMARY KEY,
    email           CITEXT NOT NULL UNIQUE,  -- alice@co.com = Alice@CO.COM
    username        CITEXT NOT NULL UNIQUE
);
 
 
-- Option 2: Functional index on LOWER()
CREATE TABLE users_lower (
    user_id         SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    username        VARCHAR(50) NOT NULL
);
 
CREATE UNIQUE INDEX uq_users_email_lower ON users_lower(LOWER(email));
CREATE UNIQUE INDEX uq_users_username_lower ON users_lower(LOWER(username));
 
-- Queries should also use LOWER() to use the index:
SELECT * FROM users_lower WHERE LOWER(email) = LOWER('Alice@Co.Com');
 
 
-- Bulk loading with constraint management
-- Step 1: Drop constraint
ALTER TABLE products DROP CONSTRAINT uq_products_sku;
 
-- Step 2: Bulk load data
COPY products FROM '/data/products.csv' CSV;
 
-- Step 3: Remove duplicates (keep first occurrence)
DELETE FROM products 
WHERE product_id NOT IN (
    SELECT MIN(product_id) 
    FROM products 
    GROUP BY sku
);
 
-- Step 4: Re-add constraint
ALTER TABLE products ADD CONSTRAINT uq_products_sku UNIQUE (sku);

Summary: UNIQUE Constraint Mastery

We've explored UNIQUE constraints comprehensively—from theoretical foundations through practical patterns to performance considerations. Let's consolidate the essential knowledge:

Key Takeaways

•UNIQUE ≠ PRIMARY KEY: UNIQUE allows NULLs (behavior varies by DBMS), multiple per table, and implements alternate/candidate keys.
•NULL Behavior Varies: PostgreSQL allows multiple NULLs; SQL Server/MySQL allow only one NULL. Test your target database.
•Column or Table Level: Single-column uniqueness can use inline syntax; composite uniqueness requires table-level declaration.
•Partial/Filtered Indexes: Enforce uniqueness only on rows matching a condition (active users only, published articles only). PostgreSQL and SQL Server support this.
•Can Be FK Target: Foreign keys can reference UNIQUE columns, enabling natural key relationships without joining for common lookups.
•Creates Index Automatically: UNIQUE constraints create indexes, benefiting reads but adding overhead to writes.
•Common Patterns: Login identifiers, URL slugs, business keys, single-active records, multi-tenant scoped uniqueness.
•Performance Aware: Wide unique columns create large indexes. Consider case sensitivity, partial indexes, and bulk load strategies.

What's Next:

Page Complete

3 / 5