A rule requires that at most one row per customer may have an empty (NULL) discount code — that is, uniqueness must treat two NULLs as equal. What options does a relational database give you, and what does each cost?

## Why this needs deliberate work Standard uniqueness compares with three-valued logic: `NULL = NULL` is unknown, so NULL rows never collide. That default is correct for "the value genuinely does not exist and several rows may lack it". It is wrong when the absence itself is a state that must be unique — one active row, one default address, one un-revoked token, one row with no discount code. ## Option 1 — declare NULLs non-distinct SQL:2023 added an explicit `NULLS DISTINCT` / `NULLS NOT DISTINCT` qualifier on unique constraints and unique indexes; PostgreSQL implemented it in version 15. Writing `UNIQUE NULLS NOT DISTINCT (customer_id, discount_code)` makes the engine treat NULL as a comparable value for the purposes of this key, so two `(42, NULL)` rows collide. Strengths: the intent is declared in the DDL rather than encoded in data, NULL keeps its meaning everywhere else in the schema, and there is exactly one object to maintain. It works naturally for composite keys, where the sentinel approach gets awkward. Weaknesses: availability. Older versions and most other engines do not accept the clause, so migrations become engine-specific. And it flips a semantic that readers assume — anyone auditing the schema must notice the qualifier. Note the mirror-image situation: on Microsoft SQL Server, non-distinct NULLs are the *default* behaviour for unique constraints and unique indexes, so this rule needs no work there, while the opposite rule ("allow many NULLs") is the one requiring a filtered index on `WHERE col IS NOT NULL`. ## Option 2 — sentinel value Make the column `NOT NULL` and reserve one value for "none". A plain `UNIQUE (customer_id, discount_code)` then enforces the rule on every engine, because there are no NULLs left in the key. This is the most portable answer and, for some columns, the more honest model: if "no discount code" is a real business state rather than missing information, representing it as a value is arguably more correct than representing it as unknown. Costs to state explicitly: - **Query noise.** Every read must know the sentinel; `WHERE discount_code IS NULL` becomes `WHERE discount_code = ''` or `= 0`, and forgetting it produces wrong results rather than errors. - **Aggregate and join semantics change.** `COUNT(discount_code)` now counts the sentinel rows; `IS NULL` checks in application code go dead. - **Sentinel collision risk.** The chosen value must be impossible as real data. `0` for an id and `'0001-01-01'` for a timestamp are usually safe; empty string for free text often is not — and on Oracle an empty string *is* NULL, so it cannot be used as a sentinel there at all. - **Type reach.** Some types have no natural impossible value, which pushes you toward one of the other options. ## Option 3 — partial unique index over the NULL rows Keep NULL as "unknown" and add a second object enforcing the extra rule: ``` CREATE UNIQUE INDEX cust_null_code_idx ON discount (customer_id) WHERE discount_code IS NULL; ``` Combined with the ordinary `UNIQUE (customer_id, discount_code)`, you get: one row per customer per code, and at most one codeless row per customer. Each object states one rule, which reads well in review. Costs: two objects that must be changed together; the partial index is not a foreign-key target; and if the key has several nullable columns, the number of predicates you need grows combinatorially — that is where the declared-non-distinct clause clearly wins. ## Choosing - Single engine, modern version, and NULL genuinely means "unknown" elsewhere → use the `NULLS NOT DISTINCT` qualifier. - Schema must run on several engines, or the migration tooling must stay vendor-neutral → sentinel with `NOT NULL`, and document the sentinel in the column comment. - Only one nullable column and you want the DDL to read as two explicit business rules → partial unique index. Whatever you choose, apply it during a migration only after checking that existing data satisfies the new rule: if two codeless rows already exist for a customer, the constraint or index build fails, and you need a product decision about which row survives before the schema change can land. ## The deeper modelling question Often the reason a nullable column needs unique NULLs is that two different facts have been packed into one column — a code, plus a flag saying "this is the customer's default row". Splitting them (an explicit boolean or state column, uniquely constrained via a partial index; or a separate table holding exactly the special rows with a `NOT NULL` key) removes the awkwardness altogether and usually reads better a year later.