A unique constraint covers exactly the rows where every column in its key has a value. Rows with a NULL anywhere in the key are exempt, and they are exempt from each other too, which is why the same combination can repeat.

That sentence describes a correct design and a production incident equally well. The difference is not in the schema - the two are indistinguishable in a dump - it is in whether the exempt rows are the ones you meant to exempt.

Why does my unique constraint allow duplicate rows?

Because uniqueness is decided by comparison, and NULL does not compare. NULL means unknown, so asking whether one unknown equals another unknown cannot return true, and rows the engine cannot prove identical are not duplicates. PostgreSQL’s documentation states it without hedging: “two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns.”

The clause that catches people is at least one. A unique over (tenant_id, external_ref, archived_at) is fully enforced for every row where all three are populated, and inert for every row where archived_at is NULL. The constraint is not weakened proportionally. It is switched off, row by row, wherever any key column is empty.

This is ANSI SQL behaviour rather than an engine quirk. Which means the interesting question is not “does this schema contain NULLs in a unique key” - plenty of good ones do - but “which rows did I just exempt, and did I mean to?”

When a nullable column in a unique key is the correct design

Two shapes come up constantly, and in both the exemption is the entire point.

An optional unique field. A phone column with a unique constraint, nullable, is the standard way to model “at most one account per phone number, and a phone number is not required.” Most users have no phone on file. Multiple NULLs is not a loophole here, it is the requirement: the alternative is NOT NULL with invented placeholder values, which pushes the absent case into the data as a fake phone number nobody can distinguish from a real one. The constraint does its actual job perfectly - every populated number appears once - and the rows it exempts are precisely the rows that have nothing to be unique about.

A composite key with a deliberately nullable side. Consider a training system recording attendance, with a unique over (member_id, training_session_id) and member_id nullable, because some sessions accept anonymous attendees who give nothing but a nickname to keep participation private. Registered members get exactly what the constraint promises: one attendance row per member per session, no double booking. Anonymous rows carry member_id NULL, so they do not collide with each other, and a session can hold as many of them as show up. That is not the constraint failing. That is one constraint expressing two rules - identified attendance is unique, anonymous attendance is uncounted - and doing it without a second table or a trigger.

Both designs would be made worse by “fixing” them. There is no partial index to add, no sentinel to invent, nothing to tighten. The right response to a report about either one is to note that it is intentional and move on.

When the exemption is a hole you did not mean

The same mechanism turns dangerous when the nullable column arrives in the key for an unrelated reason.

The common case is soft deletion. A team has UNIQUE (tenant_id, email), adds deleted_at for soft deletes, and extends the constraint to (tenant_id, email, deleted_at) so a deleted account frees its email for reuse. It reads sensibly. But deleted_at is NULL for every live row, which means the constraint now covers only deleted accounts, and the live rows - the ones the rule existed to protect - are the exempt ones. The uniqueness was not relaxed for deleted accounts. It was relaxed for everyone else.

The second case is subtler, and there is a seventeen-year-old PostgreSQL bug report that documents it exactly. On 23 August 2009, running PostgreSQL 8.3.7, a user filed BUG #5005 after ALTER TABLE table1 ADD CONSTRAINT unique_county_year_idnumber UNIQUE (county, year, idnumber) was rejected on a table already carrying data. He had read the documentation about NULLs being exempt and expected his rows to slip through. The duplicates the engine found instead: five rows for AD in 2009, five for BD, four for LR, three for DV, three for EL.

It was not a bug, and the reason is the sharpest detail in the story. The offending rows he printed show idnumber holding the empty string rather than NULL. The exemption he was relying on never applied, because '' is an ordinary value and compares equal to itself perfectly well. Absence had been written down two ways in the same column - some rows NULL, some rows '' - so the constraint’s behaviour depended on which spelling a given row happened to receive from whichever form or import wrote it.

That is the real failure mode, and notice that it is not “a nullable column is in a unique key.” It is that nobody decided. The exemption arrived as a side effect of a schema change, or of two code paths disagreeing about how to write down “empty,” and no reader afterwards can tell an intentional exemption from an accidental one.

How each database treats NULLs in a unique constraint

The behaviour is not uniform, and the exception is the one people are least likely to expect:

So the same DDL, moved between two engines your team both runs, changes what it guarantees. The training-attendance design above works on PostgreSQL, MySQL and SQLite and breaks on SQL Server, where the second anonymous attendee in a session is rejected as a duplicate. Nothing in the migration output announces the difference.

How to say which behaviour you meant

If the exemption is intended, the best thing you can do is make it legible, because the next reader has no way to distinguish your decision from an oversight. A named constraint helps (attendance_member_session_uniq invites the question), and a comment or a description on the entity settles it.

If the exemption is not intended, there are three fixes, in rough order of portability:

• Make the column NOT NULL with an explicit sentinel. The most honest option, because it forces the absent case to have a name - archived_at becomes status, and the rule becomes statable.
• Add a partial unique index for the NULL case. Keep the main constraint and add, for example, a unique index on (tenant_id, email) filtered to WHERE deleted_at IS NULL. Two indexes, two clearly-stated rules. Available on PostgreSQL, SQLite and SQL Server; MySQL has no direct equivalent.
• Declare NULLS NOT DISTINCT on PostgreSQL 15 or later, so NULLs compare equal for this constraint alone. Shortest to write, and PostgreSQL-only.

The link table with no key at all

One case in this family is not ambiguous, and it is worth separating from the rest. A link table is a table whose whole job is to record that two rows are paired, carrying nothing of its own beyond the two foreign keys and perhaps a timestamp. Foreign keys check that the referenced rows exist; they have no opinion about how many times the pairing occurs. So a link table with two foreign keys and nothing covering the pair has no uniqueness at all - not an exemption for some rows, an absence for every row.

The consequence goes further than a bloated table. In an SQLAlchemy discussion from October 2022, a user found that adding the same object twice to a relationship wrote two junction rows, then discovered the ORM would not read them back or update them coherently. The maintainer’s explanation is the part to remember: “once it inserts those rows, it has no way to individually manipulate them”, because it is “impossible in SQL to delete just one of the rows, and not the other”. Two identical rows with no key between them are not addressable - there is no WHERE clause that selects one and not its twin.

Even here, fairness applies: the maintainer’s advice was not “add a constraint and move on” but to use an association object with its own identifier if you genuinely want the same pairing more than once. A link row that can legitimately repeat is a row that needs an identity. What is never right is repeating by accident.

The ordinary fix is a key over the pair, and there are two correct shapes. A composite primary key over the two foreign keys, when the row is nothing but the pairing. Or a surrogate id plus a unique constraint over the pair, when the row has its own lifecycle or gets referenced elsewhere. Both enforce the identical guarantee, and which one to reach for depends on whether the pairing has a life of its own.

What a schema lint pass reports, and what it refuses to conclude

Everything above is mechanical to detect and easy to forget to check, on every table, every time the schema changes - which makes it a job for a linter. The catch is that a linter which calls the phone-number constraint a defect is a linter you turn off in a week, and then it catches nothing at all.

Schemity’s schema lint is built around that constraint on itself: it reports consequences, not judgements. Here is the finding this whole post is about, on a real diagram:

The headline states the consequence - this constraint does not constrain - and the line under it names exactly which rows are exempt: “rows repeating the same values are accepted whenever field1 is NULL.” That second sentence is the one that matters, because it is the sentence you check your intent against. If field1 is the anonymous attendee’s member_id, you have just read a description of the feature and you move on. If it is a soft-delete timestamp, you have just read the incident report early.

The single-column optional unique - the phone number case - is not reported at all. The rule applies only to multi-column keys, because a lone optional unique has no ambiguity to surface.

Findings are grouped by consequence rather than graded on a severity scale, and this one sits under “Not enforced” alongside the link table with no key over its pair, and the uniqueness that covers the wrong columns. In the screenshot that group carries two findings and the “Fails at runtime” group carries two more, each group headed with its own count, and the flag in the toolbar wears a badge reading 4 whether or not the drawer is open. Grouping by consequence is what keeps the report readable: it separates what will fail at runtime from what is merely unenforced from what is a convention worth confirming, without ranking anybody’s schema.

And because designs like the anonymous-attendance key exist, every finding carries an Ignore next to its Show on canvas - visible on each row in the screenshot above. Ignoring is per finding and per diagram, and the ignore is saved in the file, so it travels with the schema and shows up in a pull request like any other change. That is the part that matters for a deliberate choice: the team records the decision once, in the place the schema lives, instead of every new reader rediscovering the constraint and re-arguing it. When the fix is an ALTER, the migration SQL diff shows the statement before it runs, which on a populated table is where you find out whether the duplicates are already there.

The schema is where the decision survives

A unique constraint is not a verdict about whether NULLs are allowed. It is a statement about which rows have to be distinguishable, and rows with nothing to distinguish are exempt by construction. Read that way, the phone number, the anonymous attendee, and the soft-delete accident are the same mechanism producing three different outcomes, and only one of them is a bug.

What separates them is whether anybody decided, and whether the decision is written down anywhere the next reader will look. That is an argument for putting uniqueness in the schema and describing it there - not in a service, not in a validator, not in the head of whoever wrote the migration.

The wider point is that this check belongs to a class of its own: a migration linter reads the statement you are about to run and a schema linter reads the model you already have, and a unique constraint weakened by a nullable column is only visible to the second, because the constraint and the nullability that disarms it are two separate declarations.

If you want the neighbouring cases: the choice between a composite primary key and a surrogate id on a junction table is the decision the link-table fix depends on, uniqueness is also what decides whether a relationship is 1:1 or 1:N rather than how the line was drawn, and an array column skips the junction table altogether along with every constraint that came with it. For a worked example of the surrogate-id shape, a membership row sitting between users and tenants earns its own id precisely because other tables point at it.