Because it’s very common for attributes to be optional but unique if specified e.g. your fleet management might have a 0-1:1 relationship between employees and cars, no car can be associated with 2 employees, an employee can’t have two cars, but any number of employee can be car-less, or car employee-less (lending pool, or cars which have not been attributed yet).

This also fits perfectly well with the normal interpretation / treatment of sql null as “not a value”.

But an employee can't be car-less twice!

That's what this change prohibits.

> But an employee can't be car-less twice!

What.

> That's what this change prohibits.

This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before).

>This change prohibits nothing, it allows specifying something (which was a bit complicated to enforce before).

It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs.

Say you have a table (EmployeeName, CarID). You could do a UNIQUE constraint on those two attributes, but that would still allow:

EmployeeName | CarID

Jeff | 2

Kim | NULL

Kim | NULL

Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense.

Hence the new constraint.

Kim being car-less would be represented by Kim not appearing in this table at all. The design you've presented makes no sense.

> It allows specifying a constraint - that you can't have two rows with the same values even if one of the values is a NULL. That's prohibiting duplicate NULLs. The change allows you to prohibit duplicate NULLs.

Sure.

> Here, "Kim" is car-less (NULL in the CarID field) twice, which makes no sense.

The relation makes no sense to start with, it didn’t need duplicate nulls for that.

pre postgres 15 can you solve this with two unique constraints ?

unique (EmployeeName), unique (EmployeeName, CarID)

You’d most likely solve this by making the CarID NOT NULL because there’s why would you even have a row with no useful data?

And if this is your employee table, you’d have UNIQUE (EmployeeData) and UNIQUE (CarID), and it would do what you want.

That's not quite the same, since the previous solution of unique (EmployeeName, CarID) allowed for one employee to have 2 separate cars. You have to use conditional indexes to solve this.

It's a 0-1:1 relationship, so you'd have columns like "name", "email", "car", etc. on the employees table, and "car" might have a unique index (which is probably an ID referencing a "cars" table).

Since employees can only have one car and since a car can only be used by one employee, an unique index is fine, but there may be 20 people that don't have a car so you don't want NULL values to be unique.

Of course there are always ways to work around this, but this is the general idea.

Multiple NULLs in a unique index are useful when an entity has 0 or 1 of a simple scalar value. For example, you might have a column with an external ID, such as a facebook login id in your users table. Not all users have a facebook id, but those who do should have a unique value in that column.

You might want to look at a unique partial index: "CREATE UNIQUE INDEX foo ON users(facebook_id) WHERE facebook_id IS NOT NULL".

I am not sure if this really is a great modelling. If a user happens to have two logins in your table (maybe they signed up with two different emails), and then connects one of his accounts with his FB identity, what does it mean that the other entry cannot be connected to the FB identity?

Lets say you have a Person table with with a SSN column. The column has a unique constraint because no two people have the same SSN. But the column in nullable, since not everyone has a SSN.

It would be weird to expect that only one person does not have SSN.

I can't think of a situation where I'd want "only a single NULL"; that seems much weirder. Another comment described a tree data structure where it could make sense, but I can't think of others. Nullable unique indexes usually describe some kind of optional identifier; if it's present, you want it to be unique, but it's perfectly fine for more than one row to omit it.

You might want an index like this on some aggregate table. Like, shoe_sales_total, which is grouped by the id of the shop assistant that helped you with the selection. But customers that have marched straight to the till and asked for shoe polish result in records with a NULL for agent_id. With a regular unique constraint (month, agent_id), your aggregate table can end up with two records for (May-21, NULL).

Of course, the workaround for such cases is simple: have a sentinel value for unassisted sales and make the column not nullable.

Such "tables" would more typically be constructed as views (materialised if performance requires it) over underlying data. In this case the underlying data would have a row for each sale, with a normal nullable column for agent_id. The query that constructs the view would use GROUP BY, and since NULLs group together in GROUP BY it would work as you expect (grouping "no agent" sales together into a single record in the view). The database system thus handles ensuring data consistency, merely by using its features as they are designed to be used.

One way nullable columns are often used is like an option type. It's easy to see that you might want rows to be unique if they have Some(value) but have multiple rows where the column is None

I agree that this is often the use case but depending on how the query (as well as how sparse the nulls are, the total data size and partitioning scheme I guess) it's also possible to achieve this through a join-table without introducing NULL values.

Unless you outer join, in which case NULL values pop up out of thin air...

Good point. I did try to say that whether that's a viable technique would depend on your query (but omitted some verb that should have made it better resemble a sentence in English :)).

But yes, even if no nulls are present in any table, outer join could (or maybe even should) introduce them.

Some kind of secondary identifier, but only for records where it is applicable (e.g. loyalty card number).

> Why would you ever want multiple NULLs in an unique index?

You would maybe want some value to be unique if it exists.

The rows (1, NULL), (1, NULL), and (1, NULL) - each NULL signifies an absence of a value - these NULLs may actually be later filled into (1, 1), (1, 2), and (1, 3). That's why may not know if any two NULL values are equal to one another.

In some cases the above is actually important, whereas in others you may want to treat NULL as a "value" instead.

E.g. if you have a "tax ID" column, and two people in your database don't have tax IDs because they're not <your country> tax payers.

Or "payment vendor processing ID". You may have multiple entries not yet sent to the vendor, so they're NULL.

This shows up literally all the time in SQL schemas.