1089017 - Add unique constraint to flaginclusions/flagexclusions

Status: NEW

(Bugzilla :: Database, enhancement)

Description

[Dylan Hardison [:dylan] (he/him)]

While working on bug 1088834, I noticed something that may be a bug:

flaginclusions has three columns (type_id, product_id, component_id).
As a component always belongs to a product, the product_id should be in agreement with the component's product id. We can't write a constraint for that in SQL of course.

However, it is true that there should only be one row per type_id and component_id. That can be declared as a UNIQUE constraint over (type_id, component_id).

This is equally applicable to flagexclusions.

Of course, Bugzilla itself cannot get into this state itself, one must directly manipulate the database to get into any inconsistent state. Still, the price of a UNIQUE constraint is worth the peace of mind, no?

Unless, of course, I am overlooking a legitimate situation where you could have the same type_id and component_id for multiple products.

Comment 1

[Frédéric Buclin]

(In reply to Dylan William Hardison [:dylan] from comment #0)
> Unless, of course, I am overlooking a legitimate situation where you could
> have the same type_id and component_id for multiple products.

MariaDB [bugs]> select * from flaginclusions;
+---------+------------+--------------+
| type_id | product_id | component_id |
+---------+------------+--------------+
|       1 |          1 |         NULL |
|       1 |          2 |         NULL |
|       1 |          8 |         NULL |

When a flagtype applies to a whole product, component_id is set to NULL. But this doesn't violate the UNIQUE constraint, at least not in MySQL, AFAIK.

Comment 2

[Dylan Hardison [:dylan] (he/him)]

UNIQUE over nullable columns is consistent on over all SQL databases, related to the assertion that NULL != NULL. In fact, often people have to work around this by using a sentinel value instead of NULL.

I tested UNIQUE (1, NULL) on sqlite3 and postgres, the behaviour is the same.