UNIQUE Constraint in SQL
Introduction
The SQL UNIQUE constraint prevents duplicate protected key values in a column or combination of columns. It turns a business statement such as "no two employees share an employee number" or "a student enrolls in a course only once" into a rule enforced by the database for every writer.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
email VARCHAR(254) NOT NULL UNIQUE
);
If john@example.com already exists, a second row using the same protected email is rejected. UNIQUE is simple to declare, but reliable production use requires decisions about NULL, case sensitivity, normalization, tenant scope, historical reuse, soft deletion, concurrent writes, and migration of existing data.
Why UNIQUE Exists
Many attributes identify a business object independently from its primary key: username, employee number, product SKU, order number, invoice number, vehicle VIN, provider transaction reference, or external account ID. Allowing duplicates can make ownership ambiguous, apply payments twice, create duplicate accounts, and break integrations.
Application checks are useful for early feedback, but they are not an authoritative concurrency boundary. Data can enter through APIs, imports, batch jobs, scripts, migrations, and other services. The database constraint protects the invariant regardless of writer.
Basic Column and Table Syntax
A single-column rule can be declared beside the column or at table level. Naming the table-level constraint improves operational diagnostics and migration control.
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
sku VARCHAR(50) NOT NULL,
product_name VARCHAR(200) NOT NULL,
CONSTRAINT uq_products_sku UNIQUE (sku)
);
After one row stores LAPTOP-001, another protected row cannot store the same SKU. The constraint does not validate the SKU format, require a value unless NOT NULL is also present, or determine whether SKU is the correct business key.
Concurrency Makes Database Enforcement Essential
Two requests can simultaneously run a SELECT that reports a username is available. Both may then attempt to insert it. Application-only check-then-insert logic races because neither request sees the other's uncommitted change.
A UNIQUE constraint serializes the final decision according to the database's transaction rules. One write succeeds and the other receives a uniqueness violation. Applications should still pre-check for friendly feedback, but they must treat the database result as authoritative and handle the conflict safely.
UNIQUE and NOT NULL Are Separate Rules
UNIQUE answers whether duplicate protected values may exist. NOT NULL answers whether a value is mandatory. If every user requires a distinct username, apply both.
username VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(30) UNIQUE
Here username is required and unique. Phone is optional but intended to be unique when present, subject to the target DBMS's NULL behavior. Do not assume UNIQUE automatically makes a column mandatory.
NULL Behavior Is Database-Specific
Ordinary PostgreSQL and MySQL uniqueness generally allows multiple NULL values because NULLs are treated as distinct for this purpose. Modern PostgreSQL also supports NULLS NOT DISTINCT when NULL should compare as one repeated value. SQL Server commonly permits only one NULL in an ordinary single-column unique key, so a filtered unique index is often used for "unique when non-null."
Oracle has its own index behavior, including treatment of entirely NULL composite B-tree entries. Never build portable logic from a memorized universal rule. Decide the desired NULL policy, verify vendor and version behavior, and include it in automated tests.
UNIQUE Versus PRIMARY KEY
A primary key is the selected row identity and is inherently non-null. A UNIQUE constraint protects an additional candidate or business key. A table has one primary key, which may be composite, but can have several independent UNIQUE constraints.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_number VARCHAR(30) NOT NULL UNIQUE,
work_email VARCHAR(254) NOT NULL UNIQUE,
badge_number VARCHAR(30) UNIQUE
);
The generated ID provides stable internal identity. Employee number and work email remain alternate candidate keys. Uniqueness alone does not automatically make a mutable or wide business value the best primary key.
Surrogate Identity and Business Uniqueness
A common design combines a surrogate primary key with one or more business UNIQUE constraints. The surrogate remains stable when an email, SKU, or account number changes, while the unique rule still prevents duplicate business objects.
Adding an ID column without business uniqueness is a frequent mistake. It allows two rows representing the same logical customer or external transaction because their generated IDs differ. Technical row identity and entity identity solve different problems and often must both be modeled.
Composite UNIQUE Constraints
Uniqueness can apply to a combination rather than each column independently. An enrollment table may permit one student in many courses and one course with many students while prohibiting the same pair twice.
CREATE TABLE enrollments (
enrollment_id BIGINT PRIMARY KEY,
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL,
CONSTRAINT uq_enrollment_student_course
UNIQUE (student_id, course_id)
);
The rule does not make student ID or course ID individually unique. It protects only the ordered combination. Composite NULL behavior also depends on the database and each component's nullability.
Define the Correct Scope
Most uniqueness errors are scope errors. An apartment number may be unique within a building, not globally. An order number may be unique within a tenant. A product SKU may be unique within a store or catalog.
CONSTRAINT uq_orders_tenant_number
UNIQUE (tenant_id, order_number)
Write the business sentence before choosing columns: "Within one tenant, no two orders share an order number." Including too few columns makes the rule too strict; including unrelated columns makes it too weak.
More Columns Can Weaken Uniqueness
Developers sometimes add an ID or timestamp to a composite constraint to make a duplicate insert succeed. That usually destroys the intended rule because the added value differs on every row. For example, UNIQUE (student_id, course_id, enrollment_id) permits repeated student-course pairs when enrollment ID is already unique.
Use the minimal set of attributes that defines the duplicate you want to prevent. If a conflict is legitimate, revisit the business model rather than weakening the key accidentally.
Junction Tables Need Relationship Uniqueness
A many-to-many junction table often uses its two foreign keys as a composite primary key. If it uses a surrogate ID instead, add a UNIQUE rule for the relationship when duplicate links are invalid.
CREATE TABLE product_categories (
product_category_id BIGINT PRIMARY KEY,
product_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
CONSTRAINT uq_product_category
UNIQUE (product_id, category_id)
);
The surrogate identifies the row, while the composite constraint protects the logical relationship. Choose a composite primary key directly when no independent relationship identity is needed.
Name UNIQUE Constraints Explicitly
Names such as uq_users_email and uq_orders_tenant_number make schema inspection, migration scripts, logs, and incident diagnosis clearer. They also help applications map a specific violation to the correct user message without relying only on vendor-generated names.
Naming conventions should remain consistent but not expose sensitive business details to untrusted clients. Keep detailed diagnostics internally and return controlled conflict responses externally.
Add UNIQUE Only After Profiling Existing Data
A new constraint fails if the table already contains duplicates. Start with a grouped query using exactly the intended columns and equality rules.
SELECT email, COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
Also search normalized duplicates if future equality will ignore case or whitespace. Investigate why each group exists. Duplicates may represent bad imports, race conditions, legitimate shared values, historical reuse, or a wrongly defined requirement. Do not automatically delete rows.
Resolve Duplicates as a Business Decision
Deduplication may require merging relationships, selecting a canonical record, preserving aliases, repairing foreign keys, refunding duplicate transactions, or marking one account inactive. A window function can help identify technical candidates, but SQL cannot decide which real-world entity should survive.
Record lineage and audit the resolution. Fix the writer that created duplicates before enforcement, or new violations can appear during cleanup. The constraint is a final boundary, not a substitute for understanding existing data.
Cross-Database Addition Syntax
Common database systems support table-level constraint addition with similar syntax, though online-build and validation options differ.
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);
MySQL, PostgreSQL, SQL Server, Oracle, and SQLite differ in locking, index creation, NULL semantics, and supported ALTER operations. SQLite changes may require a replacement-table migration. Verify documentation for the deployed version and do not assume a small development table predicts production behavior.
UNIQUE Constraint Versus Unique Index
A UNIQUE constraint expresses a logical data rule. Databases usually enforce it with a unique index. A unique index can also enforce equality but may support specialized features such as expressions, filters, included columns, operator classes, or vendor-specific build options.
Prefer a constraint for a plain relational business key when possible because schema tools and foreign-key rules may recognize it more directly. Use a specialized index when the requirement depends on normalized expressions or conditional rows. Document the business invariant either way.
Conditional Uniqueness
Some rules apply only to selected rows. A system may allow many archived usernames but only one active row for each name. PostgreSQL partial indexes and SQL Server filtered indexes can enforce a predicate.
-- PostgreSQL example
CREATE UNIQUE INDEX uq_users_active_email
ON users (email)
WHERE deleted_at IS NULL;
MySQL and Oracle may require generated columns, function-based indexes, or alternative modeling depending on version. Ensure every writer and query uses the same definition of active status.
Soft Deletes Complicate UNIQUE Rules
If email is globally unique forever, a normal constraint is correct even after soft deletion. If a deleted account releases its email for reuse, uniqueness must apply only to active rows. Simply adding deleted_at to UNIQUE (email, deleted_at) may not work as intended because NULL treatment differs and distinct deletion timestamps permit multiple historical rows.
Define identity and reuse policy first. A filtered or partial unique index usually expresses active-only uniqueness more precisely where supported. Historical accounts, login recovery, and audit requirements may make reuse unsafe.
Case Sensitivity and Collation
UNIQUE uses database comparison rules, including collation, data type, and operator semantics. Under one collation, John and john may compare equal; under another they may differ. The correct behavior depends on whether the attribute is a username, code, natural-language label, or case-sensitive external identifier.
Changing collation later can reveal previously hidden duplicates or make formerly distinct values equal. Test realistic scripts, accents, normalization forms, and locale behavior before enforcement or migration.
Normalize Before Enforcing Semantic Uniqueness
Textual equality may not match business equality. Leading spaces, punctuation, case, and phone formatting can make the same logical value look different. Define a canonical representation before adding a unique key.
For usernames, a generated or stored normalized value can support case-insensitive uniqueness. Phone numbers may be normalized to a validated international format. Avoid aggressive email rewriting because provider rules differ. Preserve the original display value when useful while enforcing uniqueness on a carefully defined canonical form.
Expression-Based Uniqueness
Some databases can create a unique index on an expression, such as lowercase email.
-- PostgreSQL example
CREATE UNIQUE INDEX uq_users_email_ci
ON users (LOWER(email));
This enforces the chosen normalization for all database writers. Confirm function determinism, collation, Unicode behavior, and query compatibility. Other platforms may use computed or virtual columns with a unique index. Keep normalization logic centralized so application pre-checks and database enforcement agree.
Do Not Infer Uniqueness From Familiarity
Names and dates of birth are not unique. Phone numbers can be shared, recycled, or unavailable. Email addresses may be shared by families or reused under account policies. Even government identifiers can have jurisdiction, correction, or privacy constraints.
Ask domain owners whether the value is globally unique, unique within a scope, unique only while active, unique over a time period, or not reliably unique. Familiar-looking identifiers are not automatic keys.
Temporal and Historical Uniqueness
A certification number may never repeat, while a room booking must only avoid overlapping time intervals. UNIQUE handles equality of key values; it cannot generally detect range overlap. Versioned records often need UNIQUE (entity_id, version_number), but date-range exclusion requires richer constraints or transaction logic.
Do not delete history simply to free a reusable identifier without considering audit, legal, and integration effects. Model validity periods and reuse policy explicitly.
Multiple Constraints Protect Independent Rules
A table can have several unique business keys. An employee may have a unique employee number and work email. A product may have a unique internal catalog code and a unique provider mapping within each provider.
Keep independent rules in independent constraints. Combining unrelated columns into one wide key does not enforce either rule. It permits a duplicate in one field whenever another field differs.
UNIQUE and Foreign-Key Targets
A foreign key can reference a primary key and, in many databases, another candidate key protected by an eligible UNIQUE constraint. This is useful when a stable natural business key is the intended relationship target.
Surrogate keys are often narrower and easier to update, while business-key references can improve interoperability. Choose based on stability, ownership, width, and migration requirements. Ensure referenced and referencing types and comparison semantics match.
UNIQUE and Upsert Operations
Upsert syntax uses a unique key to decide whether an incoming row conflicts. PostgreSQL ON CONFLICT, MySQL duplicate-key handling, SQL Server patterns, and other vendor features differ. The conflict key must represent the correct entity identity.
INSERT INTO external_accounts
(provider, external_id, customer_id)
VALUES ('CRM', 'A-1024', 500)
ON CONFLICT (provider, external_id)
DO UPDATE SET customer_id = EXCLUDED.customer_id;
An upsert does not repair a badly scoped key. It may update the wrong entity when the uniqueness definition is incomplete.
Idempotency Keys and External IDs
APIs and event consumers use idempotency keys to make retries safe. Scope the key correctly, often by tenant, endpoint, operation, or provider. A globally unique client token may be unnecessary, while a token unique only within one request batch may be too weak.
External identifiers should commonly use UNIQUE (source_system, external_id) because different providers can issue the same text. Preserve payload hash, processing result, and timestamps when replay behavior must be audited.
Unique Constraints Have Write Cost
The supporting index consumes storage and must be checked and maintained on inserts and key updates. Random wide keys can increase page splits, memory use, I/O, and replication volume. Redundant unique constraints and indexes multiply these costs.
Integrity comes first when the business rule is real, and the optimizer can benefit from knowing a key is unique. For performance-only access paths without a uniqueness rule, use an ordinary index. Review existing primary, unique, and nonunique indexes before adding another structure.
Adding UNIQUE to Large Tables
Constraint creation may scan and sort the table, allocate temporary and permanent disk space, generate substantial logs or WAL, acquire locks, and delay replicas. Concurrent or online options reduce some blocking but are not impact-free.
Measure duplicate-detection and index-build cost on production-like data. Watch long transactions, lock queues, disk headroom, transaction logs, replication lag, CPU, and I/O. Plan rollback or fix-forward behavior if the build fails near completion.
Close the Validation Race
A duplicate query that returns no rows does not reserve the key. Another transaction can insert a duplicate before the constraint is created. Stop incompatible writers, deploy a temporary guard, use an appropriate online constraint workflow, or choose a controlled maintenance window.
Only successful authoritative enforcement closes the race. Application pre-checks and cleanup scripts are preparation, not the final integrity boundary.
Partitioning and Sharding Complicate Uniqueness
Partitioned databases may require the partition key to participate in a unique constraint because enforcement is local to partitions. That can make a globally unique order number difficult unless the partition key is part of its identity or the DBMS supports global indexes.
Sharded systems face a similar challenge across independent nodes. UUIDs reduce collision probability but do not enforce arbitrary global business uniqueness. A routing service, central registry, deterministic shard key, or distributed coordination may be required. Define whether uniqueness is global or local before choosing architecture.
Privacy, Encryption, and Hash-Based Keys
A unique index on sensitive plaintext can expose values through backups, diagnostics, or privileged access. Encryption changes equality behavior, and randomized encryption cannot usually support direct equality uniqueness. Deterministic encryption can reveal repeated values and requires careful threat analysis.
Some systems store a keyed hash or normalized fingerprint for uniqueness while protecting the original value separately. Handle collisions, key rotation, canonicalization, and access control explicitly. Security requirements can change the physical representation without changing the business rule.
Error Handling and User Feedback
A uniqueness violation is an expected conflict, not necessarily a server defect. Map the specific constraint to a controlled response such as "username is already in use" or an idempotent replay result. Avoid fragile parsing of full vendor error strings when stable error codes or structured driver fields are available.
Log constraint name, operation, correlation ID, and safe key context internally without leaking sensitive values. Retry only when the operation semantics justify it; repeating the same conflicting insert will not make the key available.
Data-Type Changes Can Change Equality
Changing a protected column's type can alter which values compare equal. Converting text to numbers may collapse '01' and '1'. Reducing decimal scale can round distinct amounts to the same value. Reducing timestamp precision can turn separate events into one duplicate key.
Profile the converted result before changing the type, not only the source data. Test overflow, truncation, rounding, collation, time-zone conversion, and index rebuild requirements. A migration can fail or silently change business identity when equality semantics shift.
Character-Set and Collation Migrations
A character-set or collation change may make previously distinct values equal through case folding, accent handling, Unicode normalization, or locale rules. A unique index rebuild can then fail even though the old constraint was valid.
Run duplicate searches under the proposed comparison rules before migration. Include multilingual test data and edge cases such as composed and decomposed characters. Coordinate application normalization with the database so pre-checks, cache keys, and constraint enforcement do not disagree.
UNIQUE Does Not Create Sequence or Immutability
A UNIQUE numeric column does not guarantee consecutive values, insertion order, or absence of gaps. Transactions can roll back, sequences can cache values, and distributed generators can allocate out of order. Use identity or sequence facilities for generation and treat gaps as normal unless the domain has a separately designed legal numbering process.
UNIQUE also does not prevent a value from being updated to another unused value. If a business key must be immutable after creation, enforce that through privileges, update logic, triggers where appropriate, or an append-only model.
Combine UNIQUE With Other Constraints
Uniqueness is only one part of validity. A product SKU may be NOT NULL, UNIQUE, and checked for an allowed format. An external mapping may combine a composite UNIQUE rule with foreign keys to provider and customer tables. A generated UUID can have a default and a uniqueness rule.
Each constraint answers a different question: is a value present, does it match the domain, does it reference a valid parent, and is the candidate key distinct? Combining precise rules produces stronger data than expecting UNIQUE to validate everything.
Equality Uniqueness Cannot Prevent Every Collision
A booking can conflict with another booking when their time ranges overlap even though their start and end timestamps are not identical. A UNIQUE constraint on room, start, and end only blocks exact duplicate intervals. Similar limitations apply to spatial regions, effective-date ranges, and resource-capacity limits.
Use exclusion constraints where supported, serialized transactions, locking, or carefully designed availability logic for overlap rules. Understand whether the business rule is equality, range intersection, total capacity, or state transition before selecting the enforcement mechanism.
Removing UNIQUE Is a Contract Migration
Dropping a uniqueness rule allows states that applications and reports may never have handled. Foreign keys may depend on the candidate key, upserts may use it as a conflict target, and caches or integrations may assume one row per value.
Identify dependencies, update consumers, decide how duplicates will be distinguished, and test query cardinality before removal. If the rule is only changing scope, create and validate the replacement before dropping the old protection whenever the DBMS and deployment strategy permit it.
Investigate Actual Duplicate Rows
A GROUP BY query identifies duplicate key groups but not which rows should be reviewed. A window function can rank rows within each group while preserving identifiers and timestamps.
SELECT *
FROM (
SELECT c.*,
ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY created_at, customer_id
) AS duplicate_rank
FROM customers c
) ranked
WHERE duplicate_rank > 1;
The ordering only identifies candidates; it does not prove later rows should be deleted. Compare ownership, activity, relationships, consent, and source lineage before merging or retiring records.
Testing UNIQUE Constraints
Test the accepted first insert, rejected duplicate insert, and update collision. For composite rules, vary each component independently. Test NULL combinations, letter case, accents, whitespace, normalized equivalents, soft-deleted rows, tenant boundaries, bulk imports, and concurrent requests.
Use two real database transactions to validate race behavior. Unit tests against an in-memory substitute may have different collation and NULL semantics. Migration tests should begin with duplicates and prove the deployment fails safely or resolves them according to policy.
Include lifecycle scenarios as well as isolated statements. Create a value, rename it, archive or soft-delete the row, attempt reuse, restore the original row, and process the same external message twice. Verify that each outcome matches the documented policy. Test error mapping for every unique constraint because a table with several candidate keys must report the correct conflict. Finally, inspect the deployed schema and supporting indexes so the test proves database enforcement exists rather than only application validation.
Safe Production Migration
- Write the exact business rule and scope.
- Choose NULL, case, whitespace, normalization, and reuse policies.
- Find exact and semantic duplicate groups.
- Resolve data with domain owners and preserve audit history.
- Fix application, API, ETL, batch, and administrative writers.
- Review existing indexes and estimate build resources.
- Test locking, logging, replication, and rollback at realistic scale.
- Close the race with a controlled enforcement workflow.
- Add the constraint or specialized unique index.
- Run concurrent write tests and monitor conflict rates.
Common UNIQUE Constraint Mistakes
- Assuming one NULL rule applies to every database.
- Using a global rule when uniqueness is tenant- or parent-scoped.
- Adding a generated ID to a composite key and weakening the rule.
- Assuming names, phone numbers, or email addresses are always unique.
- Ignoring case, whitespace, Unicode, collation, and normalization.
- Using UNIQUE only for query performance rather than integrity.
- Adding redundant constraints and supporting indexes.
- Deleting duplicates automatically without understanding ownership.
- Ignoring active-only and historical-reuse requirements.
- Relying on application pre-checks during concurrent writes.
- Building a large unique index without capacity and lock testing.
- Assuming UNIQUE guarantees format, validity, immutability, or order.
Production Checklist
- Confirm the precise duplicate that the business prohibits.
- Use the minimal key and correct tenant, parent, or provider scope.
- Decide whether values are required and add NOT NULL separately.
- Verify NULL semantics on the deployed database version.
- Define canonicalization, collation, case, and whitespace behavior.
- Specify soft-delete, validity-period, and identifier-reuse policies.
- Profile exact and normalized duplicates before migration.
- Fix all writers and close the validation-to-enforcement race.
- Review redundant indexes and estimate storage and write cost.
- Test concurrency, upsert, error handling, partitioning, and replication.
- Name constraints clearly and monitor violations by source.
- Document equality semantics and future schema-change risks.
Interview Questions and Answers
What does UNIQUE do? It prevents duplicate values for a protected single or composite key.
Does UNIQUE imply NOT NULL? No. Presence and uniqueness are separate rules, and NULL behavior varies by DBMS.
How does it differ from PRIMARY KEY? The primary key is the selected non-null row identity; UNIQUE protects additional candidate or business keys.
What is composite uniqueness? The combination of several columns must be distinct, while individual columns may repeat.
Why is database enforcement needed? Application checks can race and do not cover every writer.
How do soft deletes affect uniqueness? The business must decide whether deleted values remain reserved or uniqueness applies only to active rows.
Can UNIQUE prevent overlapping bookings? Not generally; equality uniqueness does not express interval overlap.
How do you add it safely to existing data? Define equality, profile duplicates, repair data and writers, test build impact, then enforce under controlled concurrency.
Key Takeaway
A UNIQUE constraint is the database's authoritative protection for a candidate or business key. Its value comes from enforcing the right rule under every writer and concurrent request, not merely from creating an index.
EFFECTIVE UNIQUENESS = CORRECT BUSINESS KEY
+ CORRECT SCOPE
+ EXPLICIT NULL POLICY
+ DEFINED EQUALITY
+ CONCURRENCY-SAFE ENFORCEMENT
+ CLEAN EXISTING DATA
+ TESTED MIGRATION
Start with the duplicate the business truly forbids. Keep the key minimal, define normalization and historical reuse, verify target-database behavior, and treat large-table enforcement as a production migration. A well-designed UNIQUE rule prevents ambiguity while preserving valid variation.