skip to content

You are modelling an optional identifier — most rows will not have one, but when present it must be unique — and the schema has to behave identically on PostgreSQL, MySQL and Microsoft SQL Server. How do you approach the design?

level: principalimportance: nice to knowfreq 22%

answer

  1. engines disagree: many NULLs vs one NULL
  2. split into child table with NOT NULL UNIQUE
  3. or uniform filtered index WHERE col IS NOT NULL
  4. never sentinel-fill an identifier
  5. no rule may depend on NULL-distinctness

basics

~20 s

Do not let correctness depend on engine NULL rules, which disagree. Either move the optional identifier into its own table where the column is NOT NULL and uniquely constrained, or keep it in place with an engine-specific object (filtered index on SQL Server, plain unique constraint elsewhere) chosen deliberately.

solid answer

~1 min

The trap is that the three engines disagree on the one thing this design leans on: PostgreSQL and MySQL allow **many** NULLs under a unique constraint, SQL Server allows **one**. So a plain `UNIQUE (external_id)` means different things per engine — the same data set is legal in one and rejected in another. Two defensible designs: 1. **Split the attribute into its own table.** A child table holding only the rows that have an identifier, with the column `NOT NULL` and a unique constraint (or a primary key) on it. Uniqueness semantics are then engine-independent because there are no NULLs, the sparse column stops widening the main table, and the identifier gets a real key that a foreign key could target. Cost: a join, and ORM friction. 2. **Keep the column and normalise the enforcement per engine** — a plain unique constraint where NULLs are distinct, and a filtered unique index `WHERE external_id IS NOT NULL` on SQL Server. One rule, two implementations, documented in the migration. I would take the split when the identifier is genuinely sparse or carries its own attributes, and the per-engine index when the column is central to queries and a join would hurt.

code

sql · 6 lines
sql
CREATE TABLE party_external_id (
  party_id    bigint PRIMARY KEY REFERENCES party (id),
  external_id text   NOT NULL,
  source      text   NOT NULL,
  CONSTRAINT party_external_id_key UNIQUE (source, external_id)
);

go deeper

for a junior

Recognise that engines disagree about how many NULLs a unique constraint allows, so 'optional but unique' cannot be left to the default.

for a middle

Name both concrete designs — separate table with NOT NULL, or filtered unique index on rows that have a value — and why each works everywhere.

for a senior

Weigh sparsity against read cost, prefer the uniform filtered form for explicitness, and plan the migration around existing duplicates and backfill.

for a principal

Turn it into a checkable schema invariant — no uniqueness rule may depend on engine NULL semantics — and argue the modelling case that an optional unique attribute is really its own relation.

## What actually differs "Optional but unique" is one of the most common shapes in real schemas: a legacy system id, a tax number, a vanity slug, an external CRM reference. It is also precisely where engines diverge. - **PostgreSQL, MySQL/MariaDB, Oracle, SQLite** — NULLs are distinct for uniqueness. `UNIQUE (external_id)` permits unlimited rows without an identifier and rejects duplicate real values. This is exactly the desired rule. - **Microsoft SQL Server** — NULLs are non-distinct. The same declaration permits **one** row without an identifier and rejects the rest, which is not the desired rule at all; it fails on the second unidentified row. So the identical DDL implements the requirement on one engine and breaks the application on another. Any design that ships to all three must confront this rather than inherit it. ## Design A — split the optional attribute into its own relation Relational modelling says an attribute that applies to only some entities is a candidate for its own relation. Concretely: keep `party` as the main table, and add `party_external_id (party_id PK/FK, external_id NOT NULL UNIQUE)`. Rows exist only for parties that have an identifier. What this buys: - **Engine independence.** With `NOT NULL` there are no NULLs in the key, so every engine enforces the same rule. This alone resolves the requirement. - **A real key.** `external_id` is unique over the whole table, so it is a legal foreign-key target and a legal upsert conflict target. - **Density.** A sparse column no longer widens every row, and the unique index contains only the rows that have values — smaller and cheaper than an index padded with NULL entries. - **Room to grow.** Optional identifiers rarely stay bare; they acquire an issuing system, a verified-at timestamp, a source. Those attributes have a home. Costs: a join on every read that needs the identifier; more ORM mapping (usually a one-to-one optional association, which several frameworks handle awkwardly); and one more table in a schema that may already have many. If the identifier is read on the hot path of most queries, the join is a real tax. ## Design B — keep the column, normalise enforcement per engine Declare the intended rule once in the design docs — "unique when present, freely absent" — and implement it per engine: - PostgreSQL / MySQL: `UNIQUE (external_id)` is enough. - SQL Server: a filtered unique index `WHERE external_id IS NOT NULL`, which restores the many-NULLs behaviour. - Optionally use the same filtered form everywhere, so the DDL is uniform and the semantics obviously do not rely on the engine's NULL rule. This is my preferred variant of B: one shape, no hidden dependency, and it survives a future engine change. Costs: an index rather than a constraint, so it is not a foreign-key target and tooling may treat it as tuning; and the schema now has engine-conditional migration paths unless you adopt the uniform filtered form. ## What to reject - **Relying on the default and hoping.** The most common failure: developed on PostgreSQL, deployed on SQL Server, breaks on the second row with no identifier — and by then thousands of such rows exist in the source data. - **Sentinel values for identifiers.** Filling absent identifiers with `''` or `'N/A'` makes uniqueness work but poisons the data: every consumer must know the sentinel, and on Oracle `''` is NULL anyway. Acceptable for a state column, poor for an identifier. - **Application-only enforcement.** A read-then-write check races between concurrent requests and cannot survive bulk imports or direct database access. ## Choosing, and stating the choice Decide by how sparse and how central the attribute is: - Sparse (a small minority of rows) or likely to grow attributes → split it out. - Present on most rows and read on hot paths → keep the column and use the uniform filtered unique index. Then make the decision durable: record it in the schema convention, not only in the migration. The rule that must be written down is "no uniqueness rule in this schema may depend on the engine's NULL-distinctness behaviour", because that is the invariant a reviewer can check mechanically — scan for unique constraints over nullable columns and require each to be justified. ## Migration reality Whichever design wins, the existing data decides the sequencing. Adding uniqueness to a populated column fails if duplicates exist, and splitting a column into a child table needs a backfill plus a period where both copies exist and writes go to both. Neither is hard, but both need a plan before the DDL is written — and the enforcement must not be off in production while the backfill runs.

  • What is the strongest argument against splitting the optional identifier into its own table?
    Read cost and friction. Every query that needs the identifier gains a join, and if it appears in list views or search predicates that join is on the hot path. Object mappers also handle optional one-to-one associations poorly, often producing an extra query per row unless the fetch is tuned. When the attribute is present on most rows and read constantly, keeping the column with a filtered unique index is the better trade.
  • Why prefer the filtered index form even on engines where a plain unique constraint already does the right thing?
    Because it makes the semantics explicit rather than inherited. A plain constraint over a nullable column behaves differently depending on the engine's NULL rule, so its correctness is invisible in the DDL; the filtered form states 'unique among rows that have a value' outright and produces the same behaviour everywhere. The cost is that an index is not a foreign-key target, so the table needs a surrogate key for references.

saying these in an interview costs you the question

  • Assuming every engine allows many NULLs under a unique constraint
  • Filling absent identifiers with a sentinel string such as 'N/A' or an empty string
  • Leaving enforcement to application code because 'the ORM checks it'
  • Adding the uniqueness rule without first finding existing duplicates
  • Splitting the attribute out without planning the backfill and dual-write window

context

resolving links…