The unique constraint on users.Right is not applied

Erwan Rouchet
2022-05-16T12:56:57Z

Found while testing the check constraints for #887:

Using the Django admin, a Django shell, or SQL queries, it is possible to create duplicate `Right` instances. The unique constraint `right_unique_target` is completely ignored because ...

Found while testing the check constraints for #887 (closed):

Using the Django admin, a Django shell, or SQL queries, it is possible to create duplicate Right instances. The unique constraint right_unique_target is completely ignored because of NULL values.

The constraint should apply to (user_id, group_id, content_id, content_type_id), meaning that on a specific instance of a model, only one Right should exist for each combination of user ID and group ID. However, there is a check constraint to ensure that we only have either user_id xor group_id, meaning that one of them will always be NULL. Null values do not play well at all with unique constraints, because any comparison that involves a NULL in SQL does not return TRUE or FALSE but NULL.

arkindex_dev=# SELECT (1, 2, 3, 4) = (1, 2, 3, 4);
 ?column?
----------
 t
(1 row)

arkindex_dev=# SELECT (1, NULL, 2, 3) = (1, NULL, 2, 3);
 ?column?
----------

(1 row)

arkindex_dev=# SELECT ((1, NULL, 2, 3) = (1, NULL, 2, 3)) IS NULL;
 ?column?
----------
 t
(1 row)

This can be solved using two partial unique constraints.