A table declares a two-column unique constraint over (tenant_id, external_ref). Two rows are inserted, both with tenant_id 7 and external_ref NULL. Does the constraint reject the second row?

## The rule for composite keys Uniqueness over several columns compares key values row-pair-wise. Two rows are duplicates only if every constrained column is equal. Because a comparison involving NULL evaluates to **unknown** rather than true, a single NULL in either row's key is enough to prevent the pair from being declared duplicate — regardless of how many other columns match exactly. So with `UNIQUE (tenant_id, external_ref)`: - `(7, 'A')` and `(7, 'A')` — rejected. All columns equal, none NULL. - `(7, 'A')` and `(8, 'A')` — accepted. `tenant_id` differs. - `(7, NULL)` and `(7, NULL)` — **accepted** under standard semantics. The second column's comparison is unknown. - `(7, NULL)` and `(7, 'A')` — accepted, obviously. - `(NULL, 'A')` and `(NULL, 'A')` — accepted too; the NULL can be in any position. Microsoft SQL Server treats NULLs as equal for uniqueness, so the third and fifth cases are rejected there — at most one row may carry each distinct NULL-containing key combination. Oracle has a historical wrinkle worth knowing: in a composite unique index it does index rows with at least one non-null column, so `(7, NULL)` rows are stored and comparable, but they still do not collide with each other because the NULL comparison is unknown. Only an all-NULL key is left out of the index entirely in the single-column case. The observable behaviour matches the standard: duplicates with NULLs are allowed. ## The misreading that causes bugs The English sentence "unique on tenant and external reference" invites the reading "each tenant has at most one row per reference, including at most one row with no reference". The second clause is false. Concrete failure shapes: - **Import dedupe** — `UNIQUE (tenant_id, external_ref)` prevents importing the same external record twice, which is right. Locally created rows carry NULL and multiply freely, which is also right. No bug here; this is the constraint working as intended. - **State encoded as NULL** — `UNIQUE (user_id, deactivated_at)` written to mean "one active membership per user". Active rows have NULL, so they never collide. The rule is entirely unenforced, and the DDL reads as if it were. - **Optional discriminator** — `UNIQUE (account_id, currency)` where `currency` may be NULL for a default account. Two default accounts slip through. The pattern to be alert to: whenever a nullable column appears **inside** a unique key, ask what the NULL means. If NULL is data ("no external reference exists"), permitting many is usually correct. If NULL is a **state** ("not deleted", "still active"), the constraint almost certainly does not enforce the rule the author intended. ## Getting the intended rule Three options, in increasing order of intrusiveness: 1. **Make the column NOT NULL.** If the model can supply a real value for every row, composite uniqueness then behaves exactly as it reads. This is by far the most robust fix and removes the engine-dependence too. 2. **Use a non-null sentinel.** Replace NULL with a designated value that cannot occur naturally — a fixed timestamp for "not deleted", an empty-string-forbidden marker, or a zero id. Uniqueness then compares real values and collides properly. The cost is that queries must know the sentinel, and outer-join and aggregation semantics change (`COUNT(col)` now counts sentinel rows). 3. **Conditional uniqueness** over the rows where the column is NULL — for example a unique index on `(tenant_id)` restricted to rows where `external_ref IS NULL`. This enforces "at most one unreferenced row per tenant" while leaving the main composite constraint in place. Two objects, but each states one rule clearly. Some engines additionally offer a clause that declares the key's NULLs non-distinct, which makes the composite constraint behave like SQL Server's; that is convenient where available but not portable. ## Reviewing a schema for this A fast audit: list every unique constraint whose column list includes a nullable column, then for each one write out in a sentence what the NULL means. Any answer of the form "it means the row is active / not deleted / current" is a defect. Any answer of the form "the value genuinely does not exist for this row" is fine, but should be accompanied by a note that the engine's NULL rule is being relied upon — because moving the schema to an engine on the other side of the split changes the accepted data set.