In a relational database, what is the practical difference between declaring UNIQUE as a table constraint and simply creating a unique index on the same columns?
answer
- constraint = catalog object, index = structure
- engine builds an index to enforce the constraint
- FK/upsert targets look for constraints
- partial + expression uniqueness needs a bare index
- constraint owns its index — can't drop it alone
basics
~20 sBoth enforce the same rule, and engines normally implement the constraint with a unique index anyway. The constraint is a named logical object in the catalog that other features can point at (foreign keys, upsert targets, tooling); a bare index is only a physical structure.
solid answer
~50 sFunctionally they enforce the same thing: no two rows may hold the same values in those columns. Under the hood almost every engine implements a UNIQUE constraint **by building a unique index**, so the runtime cost and the read benefit are identical. The difference is catalog status and expressiveness: - A constraint is a named logical object. Foreign keys, `ON CONFLICT`/`MERGE` targets, schema diff tools and ORMs look for constraints, not indexes. - A constraint **owns** its index: you cannot drop that index independently, and dropping the constraint drops it. - A bare unique index can do things the constraint grammar cannot express — partial/filtered (`WHERE deleted_at IS NULL`), expression based (`lower(email)`), custom collation or ordering, extra included columns. Rule of thumb: if the uniqueness is part of the logical model, declare a constraint; drop to a bare unique index only when you need a capability the constraint syntax lacks.
code
sql · 4 linesALTER TABLE account
ADD CONSTRAINT account_email_key UNIQUE (email);
CREATE UNIQUE INDEX account_email_idx ON account (email);go deeper
Know that both enforce the same rule and that the constraint is the normal way to declare it; know that the engine builds an index underneath, so you get the lookup speed for free.
Explain the catalog-versus-structure distinction, the shared write cost, and the cases (partial, expression) where only a bare index works.
Add lifecycle: the constraint owns its index, cleanup scripts can't remove it, rebuilds need an adopt step, and duplicate declarations cost writes.
Frame it as a schema convention — constraints express the logical model and are referenceable by FKs and tooling; bare unique indexes are a deliberate, documented exception for conditional or computed uniqueness.
## The two objects A **unique constraint** is a declaration in the table definition: "these columns form a candidate key." It lives in the catalog as a named constraint attached to the table, alongside primary keys, foreign keys and CHECKs. A **unique index** is a physical access structure — normally a B-tree — that additionally refuses to store two entries with the same key. Its primary job in the catalog is to be an access path the optimizer can use. The reason the two are confused is that virtually every engine implements the first with the second. When you declare `UNIQUE (email)`, the engine silently creates a unique index behind it and uses that index to detect violations at insert/update time. There is no separate "constraint checking machine": the index insert either succeeds or reports a duplicate key. ## What is identical - **Enforcement semantics.** Same rows accepted, same rows rejected (including how NULLs are treated, which follows the engine's rule either way). - **Write cost.** One index maintained per declaration; every insert/update of those columns pays an index insert plus a uniqueness probe. - **Read benefit.** The optimizer can use the constraint's underlying index for equality lookups, range scans and joins exactly as it would use a hand-built one. Declaring `UNIQUE (email)` gives you a usable index on `email`; adding a separate `CREATE UNIQUE INDEX ON t(email)` on top is pure duplication — two structures, doubled write cost, no gain. ## What differs **1. Catalog identity and referenceability.** Other database features are defined in terms of constraints. The SQL standard says a foreign key must reference the columns of a primary key or unique *constraint*. Upsert clauses that name a conflict target, schema comparison tools, migration frameworks and ORM reverse-engineering all key off constraint metadata. An index that enforces the same rule is invisible to that layer in the standard, and in engines that follow it strictly. **2. Ownership and lifecycle.** A constraint owns its index. You cannot `DROP INDEX` the index that backs a constraint — you must drop the constraint, which takes the index with it. This matters when you want to rebuild a bloated index: for a bare index you can build a replacement and swap it; for a constraint's index you need the engine's "adopt this existing index as the constraint" path, or you drop and recreate the constraint (which is a heavier catalog operation and, done naively, leaves a window with no enforcement). Conversely, a constraint is harder to delete by accident: an index cleanup script that drops "unused indexes" cannot silently remove your uniqueness rule. **3. Expressiveness.** Constraint syntax is deliberately narrow: a list of plain columns. Unique *indexes* can be: - **Partial / filtered** — unique only over the subset of rows matching a predicate, e.g. one active row per email while soft-deleted rows accumulate. - **Expression based** — unique on `lower(email)` or on a normalized form, rather than on the raw stored value. - **Collation- or opclass-specific**, or with included non-key payload columns for index-only reads. Those capabilities are exactly why bare unique indexes exist as a design choice rather than an accident. The price is that such indexes are usually *not* accepted as foreign-key targets, because a partial index does not guarantee uniqueness over the whole table and an expression index does not key on the referenced columns themselves. **4. Portability of intent.** A constraint is a statement about the data model that survives a dump/restore into another engine and reads as a rule in the DDL. An index is a statement about performance that happens to have a side effect. Reviewers and future readers treat them differently, and that is a real maintenance property, not just aesthetics. ## How to choose Default to declaring the constraint when the rule is part of the logical model — natural keys, business identifiers, anything a foreign key might one day reference. Reach for a bare unique index when the rule is conditional or computed and the constraint grammar simply cannot express it, and record in the migration *why* (a comment or a naming convention) so the next reader does not mistake it for a stray performance index. Never declare both for the same columns. And when you see a unique index whose columns exactly match an existing constraint, that is duplicate work on every write.
- If a UNIQUE constraint already builds an index, is there ever a reason to add a separate index on the same leading column?Not on exactly the same column list — that is duplicated write cost for no read gain. It can make sense to add an index with the same leading column but a different shape: extra trailing columns for a composite lookup, included payload columns for index-only scans, or a different sort order for a range query. The test is whether the optimizer gets an access path it did not already have.
- Does dropping a unique constraint drop its index?Yes. The index is owned by the constraint, so dropping the constraint removes it, and you cannot drop that index on its own. That is a trap during index cleanup and during online rebuilds: to replace a constraint's index without a gap in enforcement you build the new unique index first and then have the constraint adopt it, rather than dropping the constraint and recreating it.
- A colleague enforces uniqueness only in application code and skips the constraint. What do you say?Application checks are read-then-write and race under concurrency: two requests both read 'no such email' and both insert. Only the database can make the check and the write atomic, so the constraint is the real enforcement and the application check is just a nicer error message. Keep both, but never only the second.
The constraint is the law on the books; the index is the lock on the door that actually enforces it. You can install a lock with no law behind it, but only the law is something other contracts can cite.
saying these in an interview costs you the question
- Claiming a unique constraint is checked by row-by-row scanning rather than by an index
- Adding a unique index on the same columns that already carry a unique constraint
- Believing a bare unique index cannot speed up queries because 'it is a constraint'
- Assuming a partial or expression unique index can be a foreign-key target
- Thinking the constraint's index can be dropped independently for a rebuild