6 ms·

> This fits with my mental model of how I think unique constraints should work with null values. Agree, this should be the default for unique indexes. Why wou

> This fits with my mental model of how I think unique constraints should work with null values. Agree, this should be the default for unique indexes. Why would you ever want multiple NULLs in an unique index?

mhaberl 4y ago
> Why would you ever want multiple NULLs in an unique index? You would maybe want some value to be unique if it exists.
daveoc64 4y ago
Some kind of secondary identifier, but only for records where it is applicable (e.g. loyalty card number).
phoe-krk 4y ago
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.
larsnystrom 4y ago
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.
- radiospiel 4y ago
  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?
masklinn 4y ago
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”.
- faho 4y ago
  But an employee can't be car-less twice! That's what this change prohibits.
  - masklinn 4y ago
    > 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).
    - faho 4y ago
      >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.
      - masklinn 4y ago
        > 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.
seanhunter 4y ago
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
- almog 4y ago
  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.
  - gsk22 4y ago
    Unless you outer join, in which case NULL values pop up out of thin air...
    - almog 4y ago
      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.
goto11 4y ago
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.
jhugo 4y ago
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.
- orthoxerox 4y ago
  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.
  - jhugo 4y ago
    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.
knorker 4y ago
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.

mhaberl 4y ago

> Why would you ever want multiple NULLs in an unique index? You would maybe want some value to be unique if it exists.

daveoc64 4y ago

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

phoe-krk 4y ago

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.

larsnystrom 4y ago

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.

radiospiel 4y ago
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?

masklinn 4y ago

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”.

faho 4y ago
But an employee can't be car-less twice! That's what this change prohibits.
- masklinn 4y ago
  > 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).
  - faho 4y ago
    >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.
    - masklinn 4y ago
      > 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.

seanhunter 4y ago

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

almog 4y ago
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.
- gsk22 4y ago
  Unless you outer join, in which case NULL values pop up out of thin air...
  - almog 4y ago
    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.

goto11 4y ago

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.

jhugo 4y ago

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.

orthoxerox 4y ago
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.
- jhugo 4y ago
  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.

knorker 4y ago

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.