15 ms·

But an employee can't be car-less twice! That's what this change prohibits.

by faho 4y ago

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.
  - jamesmstone 4y ago
    pre postgres 15 can you solve this with two unique constraints ? unique (EmployeeName), unique (EmployeeName, CarID)
    - masklinn 4y ago
      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.
    - ryanbrunner 4y ago
      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.
  - 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.
  - jhugo 4y ago
    Kim being car-less would be represented by Kim not appearing in this table at all. The design you've presented makes no sense.
    - deleted 4y ago
      [deleted]
Beltalowda 4y ago
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.

But an employee can't be car-less twice! That's what this change prohibits.

by faho 4y ago

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

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.
- jamesmstone 4y ago
  pre postgres 15 can you solve this with two unique constraints ? unique (EmployeeName), unique (EmployeeName, CarID)
  - masklinn 4y ago
    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.
  - ryanbrunner 4y ago
    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.
- 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.
- jhugo 4y ago
  Kim being car-less would be represented by Kim not appearing in this table at all. The design you've presented makes no sense.
  - deleted 4y ago
    [deleted]

Beltalowda 4y ago

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.