Unique Index in SQL

Introduction

A unique index is an index that prevents two rows from having the same indexed key according to the database's comparison and null rules. It combines two responsibilities: it gives the optimizer an ordered access path for suitable queries, and it asks the storage engine to reject inserts or updates that would create a duplicate key.

Unique indexes are useful for alternate business identities such as normalized email, employee code, invoice number, external transaction reference, or a combination such as tenant and username. The database enforces the rule even when several application sessions attempt conflicting writes concurrently, which application-side duplicate checks cannot reliably guarantee on their own.

Uniqueness semantics are database-specific. Collation determines whether text values compare as equal, and products treat nullable unique keys differently. This tutorial uses portable concepts, identifies SQL Server-specific filtered indexes, and compares important PostgreSQL, MySQL, Oracle, and SQLite behavior.

What Is a Unique Index?

A unique index is an ordered index structure whose complete key must be distinct for every qualifying row. A single-column unique index prevents duplicate values in one column. A composite unique index prevents duplicate tuples while allowing individual components to repeat.

Uniqueness should represent a real data rule. Creating a unique index only because the optimizer might estimate equality more precisely is unsafe unless duplicates are genuinely invalid. Integrity comes first; performance is a valuable secondary benefit.

Why Unique Indexes Are Needed

Consider account registration. If two customer rows share one login email, authentication, password recovery, notification ownership, and account management become ambiguous. A database-enforced unique key prevents that inconsistent state at the write boundary.

The same principle applies to invoice numbers, employee codes, API client IDs, and external payment references. Enforcing the rule centrally protects data regardless of which application, import job, script, or concurrent request writes it.

A Real-World Analogy

A company assigns one employee code to each worker. Before accepting a new code, the registry checks whether it already belongs to someone. If it does, the registration fails. The registry is both a searchable catalog and an enforcement mechanism.

A unique index provides the database equivalent. The analogy is useful, although a real database also handles transactions, locking, rollback, collations, nulls, and concurrent sessions.

Sample Database

CREATE TABLE customers (
    customer_id   BIGINT PRIMARY KEY,
    customer_name VARCHAR(120) NOT NULL,
    email          VARCHAR(255) NOT NULL,
    phone          VARCHAR(30),
    tenant_id      BIGINT NOT NULL,
    username       VARCHAR(80) NOT NULL,
    deleted_at     TIMESTAMP NULL
);

The primary key already protects customer_id. Additional business rules may require global email uniqueness, tenant-scoped username uniqueness, or uniqueness only among active rows.

Basic Unique Index Syntax

CREATE UNIQUE INDEX ux_customers_email
ON customers (email);

After successful creation, a second row with an equal email key is rejected. Exact error messages, transaction behavior, online-build options, name length, and drop syntax vary by product.

How a Unique Index Works

During an insert or indexed-key update, the engine searches the index for a conflicting key. If no conflict exists, it writes the new entry and row changes as one transactional operation. If a conflict exists, the statement fails and the duplicate state is not committed.

Database concurrency control coordinates competing transactions. Two sessions cannot both commit the same unique key merely because each checked before the other committed. One may wait, then succeed or fail depending on the first transaction's outcome.

Unique vs Nonunique Index

CharacteristicUnique indexNonunique index
Duplicate complete keysRejected under product rulesAllowed
Primary purposeIntegrity plus accessAccess path
Equality estimateAt most one complete non-null key matchMay return many matches
Composite columnsSupportedSupported
Write checksMust detect conflictsNo uniqueness conflict check

A city or status index is normally nonunique because many rows legitimately share those values. Email or external reference may be unique if the business domain requires it.

Unique Index vs Primary Key

A primary key is the table's designated entity identifier. It is unique and non-null, and a table has only one primary-key constraint, although that key may contain several columns. A table can have multiple additional unique business keys.

Database engines normally support primary keys through a unique index, but the constraint communicates stronger schema meaning and is the conventional target for relationships. Use PRIMARY KEY for row identity and unique constraints or indexes for alternate candidate keys.

Unique Index vs UNIQUE Constraint

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

A UNIQUE constraint declares a logical data rule. The database commonly creates a unique index to enforce it. A directly created unique index also enforces values but is expressed as a physical index object and may provide product-specific options not available through a constraint.

Prefer a constraint when modeling ordinary relational uniqueness. Use a direct unique index when features such as a predicate, expression, special ordering, or storage option require it. Constraint-owned indexes should normally be changed through constraint syntax.

Single-Column Unique Index

CREATE UNIQUE INDEX ux_products_sku
ON products (sku);

This definition makes each comparable SKU distinct. It can also support equality lookup, joins, and compatible ordering. Before creation, confirm normalization, collation, maximum length, and whether historical or soft-deleted rows should retain the key.

Composite Unique Index

CREATE UNIQUE INDEX ux_customers_tenant_username
ON customers (tenant_id, username);

The complete pair must be unique. Different tenants may use the same username, and one tenant may contain many usernames, but no tenant can contain the same comparable username twice. This pattern is common in multi-tenant systems.

How Composite Uniqueness Works

Composite uniqueness compares the tuple, not each component independently. Values (10, 'alex') and (20, 'alex') are different because tenant IDs differ. Values (10, 'alex') and (10, 'alex') conflict.

Column order affects navigation and ordering, although reversing the same complete non-null column set enforces the same tuple combinations. Choose order from lookup patterns while preserving the intended rule.

Unique Index and NULL Values

SQL treats null as unknown rather than an ordinary value, and database products apply different uniqueness rules. SQL Server generally permits one null in a single-column unique index. PostgreSQL, MySQL, Oracle, and SQLite commonly allow several rows whose unique key contains null because nulls are treated as distinct for this purpose.

PostgreSQL supports NULLS NOT DISTINCT for unique indexes and constraints in current versions, making nulls conflict. Composite nullable keys add more combinations. Always test the exact engine and version.

SQL Server Filtered Unique Index

CREATE UNIQUE INDEX ux_customers_email_not_null
ON dbo.customers (email)
WHERE email IS NOT NULL;

This SQL Server filtered index enforces uniqueness only for non-null email values and permits many rows with null email. It is a common way to model an optional alternate key. Queries must expose predicates compatible with the filter for efficient use.

Soft-Delete Uniqueness

-- SQL Server example
CREATE UNIQUE INDEX ux_customers_active_email
ON dbo.customers (email)
WHERE deleted_at IS NULL;

This rule allows an email from a soft-deleted row to be reused by one active row. That may or may not match audit, restoration, legal, and identity requirements. Decide the lifecycle semantics before implementing filtered uniqueness.

PostgreSQL Partial Unique Index

CREATE UNIQUE INDEX ux_customers_active_email
ON customers (email)
WHERE deleted_at IS NULL;

PostgreSQL calls this a partial unique index. It enforces uniqueness only among rows satisfying the predicate. Predicate expressions must be immutable enough for index use, and foreign-key targets have separate constraint requirements.

MySQL Conditional Uniqueness

MySQL does not provide PostgreSQL-style partial indexes. Conditional uniqueness is commonly modeled with generated columns whose values are null outside the enforced subset, followed by a unique index, or through schema redesign. The exact expression and null behavior require careful version-specific testing.

Do not copy SQL Server's WHERE index syntax into MySQL and expect equivalent behavior.

Unique Expression Index

-- PostgreSQL example
CREATE UNIQUE INDEX ux_users_lower_email
ON users (LOWER(email));

This prevents values that normalize to the same lowercase representation. PostgreSQL and SQLite support expression indexes with restrictions; Oracle provides function-based indexes. SQL Server commonly uses a deterministic computed column and indexes that column.

Application normalization and database comparison rules must agree. Lowercasing alone is not a complete international email identity policy.

SQL Server Computed-Column Pattern

ALTER TABLE dbo.customers
ADD normalized_email AS LOWER(LTRIM(RTRIM(email))) PERSISTED;

CREATE UNIQUE INDEX ux_customers_normalized_email
ON dbo.customers (normalized_email);

Computed expressions must satisfy SQL Server determinism, precision, ownership, and session-setting requirements for indexing. Treat normalization as a versioned data contract rather than a convenient one-off function.

Case Sensitivity and Collation

A unique index uses the column or expression's collation. Under a case-insensitive collation, Alice and alice may conflict. Under a case-sensitive collation, they may be distinct. Accent, width, kana, locale, and trailing-space behavior can also affect equality.

Test realistic multilingual values and document the identity policy. Changing collation can reveal previously hidden duplicates or alter lookup behavior.

Whitespace and Canonical Forms

Values that look identical to people may differ through leading spaces, Unicode normalization, punctuation, or formatting. Phone numbers, product codes, and email-like identifiers often need canonical representation before uniqueness enforcement.

Store both the original display form and a normalized key when necessary. Keep normalization deterministic and shared across all write paths.

Unique Clustered Index

-- SQL Server example
CREATE UNIQUE CLUSTERED INDEX cux_transactions_id
ON dbo.transactions (transaction_id);

A SQL Server unique clustered index makes complete table rows the index leaf and uses a unique clustered key. It avoids the hidden uniqueifier required by nonunique clustered keys. A table can have only one clustered index, so this choice affects every row and nonclustered locator.

Unique Nonclustered Index

A unique nonclustered index is a separate secondary access path. It is appropriate for alternate keys such as email or invoice number while the table remains clustered by its primary identifier or another key.

On a SQL Server clustered table, nonclustered leaf entries also use the clustered key as a locator. On a heap they use row identifiers.

INSERT Behavior

INSERT INTO customers
    (customer_id, customer_name, email, tenant_id, username)
VALUES
    (106, 'Grace', 'alice@example.com', 10, 'grace');

If the email already exists under the index's comparison rules, the statement fails. In a multi-row insert, product and error-handling semantics determine statement rollback, but a duplicate unique state cannot be committed.

Applications should translate duplicate-key errors into an appropriate domain response instead of exposing raw database details.

UPDATE Behavior

An update that changes a unique key must verify the new value. If another row owns it, the update fails. Changing indexed values also relocates entries, generates logs, and can affect locks and page density.

Stable business keys reduce churn, but real identifiers sometimes change. Plan conflict handling and references instead of assuming immutability without business agreement.

DELETE Behavior

Deleting a row removes its unique index entry, potentially allowing the value to be reused. Whether reuse is valid depends on identity, audit, legal, and restoration policies. Soft deletion keeps the row and may retain or conditionally release the key depending on index design.

SELECT Performance

SELECT customer_id, customer_name
FROM customers
WHERE email = 'alice@example.com';

A suitable unique index can navigate to at most one matching non-null key. The optimizer can use that cardinality knowledge in joins and plan costing. If requested columns are absent, a base-row lookup may still be required.

JOIN Performance

Joining to a unique key tells the optimizer that each row from the other input can match no more than one target row for the complete non-null key. This can improve cardinality reasoning and support efficient nested-loop seeks.

Data types, collations, nullability, predicates, statistics, and row counts still affect the chosen join algorithm.

ORDER BY and Range Access

A unique B-tree remains an ordered index and can support compatible ordering and ranges. Uniqueness does not guarantee result order without ORDER BY, and it does not make an incompatible leading-column query efficient.

Create the index for the integrity rule first, then evaluate whether its key sequence also serves important reads.

Composite Column Order

A unique index on (tenant_id, username) naturally supports tenant-only and tenant-plus-username searches. The reversed order enforces the same complete tuple uniqueness for non-null values but better supports global username-led lookup.

Choose order from real access patterns, key width, and nullable semantics without changing the intended business scope.

What Happens When Duplicates Already Exist?

Creating a unique index fails if existing rows violate the proposed key. The database does not arbitrarily choose which row to keep. This failure is valuable because it reveals that current data and the intended rule disagree.

Do not delete duplicates blindly. Determine whether they are accidental copies, legitimate history, separate tenants, formatting variants, or evidence that the rule needs a composite or filtered scope.

Finding Duplicate Values

SELECT email, COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

For composite uniqueness, group by every proposed key column. Apply the same normalization, collation, null, and predicate logic intended for the final index; otherwise the audit can miss conflicts the build will detect.

Reviewing Duplicate Rows

WITH ranked AS (
    SELECT
        customer_id,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY customer_id
        ) AS duplicate_rank
    FROM customers
)
SELECT customer_id, email, duplicate_rank
FROM ranked
WHERE duplicate_rank > 1;

This query identifies candidates but does not prove that the smallest ID is authoritative. Data owners must define merge, reassignment, archival, or correction rules before changes are made.

Safe Duplicate Cleanup

Preserve a backup or auditable staging copy, identify references, choose a canonical row, migrate dependent relationships, merge nonconflicting attributes, and verify counts before deleting or archiving duplicates. Perform the migration transactionally where feasible and test rollback.

After cleanup, create the constraint or index promptly so concurrent writes cannot recreate the problem between audit and enforcement.

Online Migration Strategy

Large production tables may require an online or concurrent index build, but feature names and restrictions differ. PostgreSQL offers concurrent index creation; SQL Server has online index options in supported versions and editions; MySQL and Oracle have their own DDL algorithms.

Online does not mean free. Plan log volume, disk, temporary space, locks, replicas, build duration, cancellation, and failure cleanup. Conflicting writes during validation must be handled by the engine's documented mechanism.

Concurrency Protection

A common application pattern checks whether an email exists and then inserts it. Two sessions can both observe no row before either inserts. Without a unique database rule, both may commit duplicates. The unique index closes that race at the shared data boundary.

The application can still perform a friendly precheck, but it must treat the insert as authoritative and handle a duplicate-key exception.

Transactions and Rollback

Uniqueness participates in transactions. If one transaction inserts a key and later rolls back, another waiting transaction may then succeed. If the first commits, the conflicting operation fails. Exact wait, deadlock, and isolation behavior depends on engine and statement order.

Keep transactions appropriately short and acquire resources consistently to reduce contention.

Upserts and Unique Keys

MySQL ON DUPLICATE KEY, PostgreSQL ON CONFLICT, SQLite conflict clauses, and product-specific merge forms rely on unique keys to identify conflicts. Their syntax, trigger behavior, affected-row reporting, and concurrency semantics differ.

An upsert must target the correct business key and update only intended columns. It is not a substitute for a well-defined identity rule.

Storage Overhead

A unique index consumes storage for keys, locators, included data, internal pages, and free space. Wide text keys and large composites reduce entries per page and increase memory, I/O, backups, and maintenance. In clustered or primary-key-organized engines, locator choices can multiply this cost.

Use the narrowest stable representation that preserves the domain. Hashing a business key can introduce collision and operational complexity unless designed very carefully.

Write Performance

Every affected insert and key update must search for conflicts and maintain the index. Deletes remove entries. The uniqueness check is necessary integrity work, but redundant unique indexes on the same key add avoidable cost.

Benchmark registration bursts, batch imports, updates, and retention with realistic concurrency, logging, and replicas.

Unique Indexes and Statistics

Index statistics describe distribution and help estimate filters and joins. Uniqueness gives the optimizer useful maximum-cardinality information for complete keys. Prefixes of a composite unique key are not necessarily unique, so estimates still depend on statistics.

Stale statistics can affect plan quality even when the integrity rule remains correct.

SQL Server Behavior

SQL Server supports unique clustered and nonclustered indexes, unique constraints, filtered unique indexes, included columns, and indexes on qualifying computed columns. A single-column unique index commonly allows one null; a filtered predicate can enforce only non-null rows.

Dropping an index that enforces a primary or unique constraint normally requires altering the constraint rather than treating it as an independent index.

PostgreSQL Behavior

PostgreSQL supports unique indexes, unique constraints, expression indexes, partial unique indexes, concurrent builds, and NULLS NOT DISTINCT. By default, null values are distinct for unique enforcement, so multiple null-containing keys can be accepted.

A unique constraint creates a supporting unique B-tree index. Some advanced index forms cannot serve as ordinary constraint targets because relational constraints have stricter requirements.

MySQL InnoDB Behavior

InnoDB supports unique secondary indexes and unique constraints. Multiple null values can generally appear because nulls do not compare equal for uniqueness. Secondary index entries include primary-key columns as row locators, so a wide primary key increases every secondary structure.

Prefix indexes on long strings may enforce uniqueness only for the indexed prefix, which may not represent complete-value identity. Collation also controls text equality.

Oracle Behavior

Oracle unique constraints are normally enforced by unique indexes, while nullable-column semantics can allow multiple rows with null components. Function-based unique indexes can express conditional behavior by returning null outside an enforced subset.

Empty strings are treated as null in Oracle SQL, an important difference for text uniqueness. Verify the deployed version and data types.

SQLite Behavior

SQLite supports unique constraints, unique indexes, expression indexes, and partial indexes. Multiple nulls are generally allowed in unique keys. Type affinity and collation determine comparison, so flexible typing does not eliminate the need for a deliberate canonical form.

Cross-Database Comparison

DatabaseDefault nullable uniquenessConditional or expression option
SQL ServerCommonly one null for one unique columnFiltered index; computed column
PostgreSQLMultiple nulls by defaultPartial/expression index; NULLS NOT DISTINCT
MySQL InnoDBMultiple nulls generally allowedGenerated-column patterns
OracleMultiple null-containing keys generally possibleFunction-based index patterns
SQLiteMultiple nulls generally allowedPartial and expression indexes

Checking Unique Indexes

-- SQL Server example
SELECT
    i.name,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.has_filter,
    i.filter_definition
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('dbo.customers')
ORDER BY i.index_id;

Review key order, included values, collation, predicate, constraint ownership, and usage. Index names alone do not reveal the complete rule.

Dropping a Unique Index

DROP INDEX ux_customers_email ON customers;

Syntax varies. Before removal, confirm that the index does not enforce a constraint, support a foreign key target, or protect an active business rule. Removing it can permit duplicates immediately. Script recreation and monitor all write paths.

User Registration Use Case

A registration system may enforce one normalized email globally or one username per tenant. The database rule must match login and account-recovery semantics. Soft deletion, aliases, case folding, and email changes require explicit lifecycle decisions.

Employee Management Use Case

An employee code is often a stable alternate key while an internal numeric ID remains the primary key. A unique nonclustered index enforces the code and supports lookups. Reusing departed employees' codes may violate audit rules even if technically possible.

E-Commerce Catalog Use Case

SKU may be globally unique or unique only within a merchant, brand, or marketplace. A composite unique key should represent the real scope. Formatting and case normalization must be consistent across imports and application writes.

Banking Transactions Use Case

External payment reference or idempotency key prevents accidental duplicate processing. It may need to be unique per provider or account rather than globally. Retention must preserve the key long enough to cover retry and reconciliation windows.

Software Testing Use Case

A test case might be unique by project and external case code, while an execution may be unique by run and case. Composite constraints prevent accidental duplicate records during parallel import. Retries and parameterized iterations may require another component in the tuple.

Inventory Use Case

Current balance can be unique by warehouse and product, while movement history intentionally contains repeated pairs. Applying uniqueness to the history table would reject legitimate events. Put the rule on the entity whose cardinality actually requires one row per combination.

Common Mistakes

  • Relying only on application-side duplicate checks.
  • Using a unique index where duplicates are legitimate.
  • Confusing primary identity with alternate uniqueness.
  • Ignoring nullable-key behavior across database products.
  • Assuming case, accents, spaces, and Unicode compare identically.
  • Creating a unique index before auditing existing duplicates.
  • Deleting duplicates without migrating dependent rows.
  • Using global uniqueness when the rule is tenant-scoped.
  • Releasing soft-deleted keys without business approval.
  • Creating redundant unique indexes over constraint-owned keys.
  • Ignoring write contention and import behavior.
  • Copying filtered or expression syntax between engines.

Best Practices

  1. Define the business identity and scope before choosing syntax.
  2. Prefer primary and unique constraints for ordinary relational rules.
  3. Use direct unique indexes for predicates, expressions, or physical options.
  4. Normalize values consistently across every write path.
  5. Document collation, null, case, and soft-delete semantics.
  6. Audit and resolve existing conflicts before deployment.
  7. Keep keys narrow, stable, and no wider than the domain requires.
  8. Handle duplicate-key errors as expected domain outcomes.
  9. Test concurrent writes and production-scale migrations.
  10. Review constraint ownership before altering or dropping indexes.

Interview Questions and Answers

How would you define a unique index?

It is an index that rejects duplicate complete keys under the database's comparison and null rules while providing an ordered access path.

How does it differ from a primary key?

A primary key is the table's single designated non-null identity; a table can have several alternate unique keys.

How does a unique constraint differ from a unique index?

The constraint declares a logical data rule and is commonly backed by a unique index; a direct index is a physical object that can expose additional product features.

Can a unique index contain multiple columns?

Yes. It prevents duplicate tuples while allowing individual column values to repeat.

Can unique keys contain NULL?

Often yes, but the number and comparison behavior differ significantly among database engines and options.

Why is a database rule better than an application precheck?

The database coordinates concurrent writers atomically, while separate prechecks can race.

Can uniqueness be conditional?

Yes in products supporting filtered or partial unique indexes, or through carefully designed generated, computed, or expression keys.

Does uniqueness guarantee query output order?

No. Queries need an explicit ORDER BY whenever sequence matters.

Practical Exercises

  1. Create a single-column unique index and attempt a duplicate insert.
  2. Create tenant-scoped composite uniqueness and test repeated usernames.
  3. Test null behavior on the database engine you use.
  4. Compare case-sensitive and case-insensitive collations.
  5. Create conditional uniqueness for active rows where supported.
  6. Find duplicate candidates with GROUP BY and inspect dependencies.
  7. Run two concurrent inserts for the same key and observe outcomes.
  8. Inspect whether a constraint owns the supporting index before removal.

Design Checklist

AreaReview question
IdentityWhat real business entity or tuple must be unique?
ScopeIs uniqueness global, tenant-scoped, active-only, or time-bound?
ComparisonHow should case, accents, spaces, and Unicode compare?
NullsAre missing values allowed, and should they conflict?
HistoryCan deleted or archived keys be reused?
MigrationAre duplicates and dependent references resolved safely?
ConcurrencyDoes the application handle duplicate-key outcomes?
OwnershipShould this be a primary key, constraint, or direct index?

Quick Reference

-- Single-column uniqueness
CREATE UNIQUE INDEX ux_customers_email
ON customers (email);

-- Composite tenant-scoped uniqueness
CREATE UNIQUE INDEX ux_customers_tenant_username
ON customers (tenant_id, username);

-- SQL Server optional unique value
CREATE UNIQUE INDEX ux_customers_email_not_null
ON dbo.customers (email)
WHERE email IS NOT NULL;

-- PostgreSQL nulls compare as equal for uniqueness
CREATE UNIQUE INDEX ux_customers_phone
ON customers (phone) NULLS NOT DISTINCT;

Key Takeaway

A unique index is both an integrity boundary and an access path. It prevents duplicate complete keys under the deployed database's collation, expression, predicate, and null semantics, while helping the optimizer perform equality lookups and reason about cardinality. Single-column keys protect simple identifiers; composite keys protect scoped business tuples.

Choose uniqueness from the data model, not as a performance shortcut. Decide how nulls, case, normalization, soft deletion, history, and concurrency should behave; clean existing conflicts safely; and deploy with realistic operational planning. Prefer constraints for ordinary relational rules and direct indexes for advanced conditional or expression-based enforcement. The strongest design is one whose business meaning remains clear long after the DDL was written.