Contrainte unique Postgres sur les groupes de lignes

Question

J'utilise postgresql 10.12

J'ai étiqueté des entités. Certains sont standard, d'autres non. Les entités standard sont partagées entre tous les utilisateurs, alors que les entités non standard appartiennent à l'utilisateur. Disons donc que j'ai une table Entityavec une colonne de texte Labelet une colonne user_idqui est nulle pour les entités standard.

CREATE TABLE Entity
(
  id uuid NOT NULL PRIMARY KEY,
  user_id integer,
  label text NOT NULL,
)

Voici ma contrainte: deux entités non standard appartenant à des utilisateurs différents peuvent avoir la même étiquette. Les étiquettes d'entités standard sont uniques et les entités d'un utilisateur donné ont des étiquettes uniques. Le plus dur est: une étiquette doit être unique au sein d'un groupe d'entités standard + les entités d'un utilisateur donné.

J'utilise sqlAlchemy, voici les contraintes que j'ai faites jusqu'à présent:

__table_args__ = (
    UniqueConstraint("label", "user_id", name="_entity_label_user_uc"),
    db.Index(
        "_entity_standard_label_uc",
        label,
        user_id.is_(None),
        unique=True,
        postgresql_where=(user_id.is_(None)),
    ),
)

Mon problème avec cette contrainte est que je ne garantis pas qu'une entité utilisateur n'aura pas d'étiquette d'entité standard.

Exemple:

+----+---------+------------+
| id | user_id |   label    |
+----+---------+------------+
|  1 | null    | std_ent    |
|  2 | 42      | user_ent_1 |
|  3 | 42      | user_ent_2 |
|  4 | 43      | user_ent_1 |
+----+---------+------------+

Ceci est une table valide. Je veux m'assurer qu'il n'est plus possible de créer une entité avec étiquette std_ent, que l'utilisateur 42 ne peut pas créer une autre entité avec étiquette user_ent_1ou user_ent_2et que l'utilisateur 43 ne peut pas créer une autre entité avec étiquette user_ent_1.

Avec mes contraintes actuelles, il est toujours possible pour les utilisateurs 42 et 43 de créer une entité avec étiquette std_ent, ce que je souhaite corriger.

Une idée?

Answer 1

Si votre ou vos contraintes uniques empêchent les utilisateurs de saisir des étiquettes en double pour leurs propres "entités utilisateur", vous pouvez les empêcher d'entrer l'étiquette d'une "entité standard" en ajoutant un déclencheur.

Vous créez une fonction…

CREATE OR REPLACE FUNCTION public.std_label_check()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$
    begin
        if exists(
                select * from entity 
                where label = new.label and user_id is null) then
            raise exception '"%" is already a standard entity', new.label;
        end if;
        return new;
    end;
$function$
;

… Puis attachez-le comme déclencheur à la table

CREATE TRIGGER entity_std_label_check
BEFORE INSERT 
ON public.entity FOR EACH ROW
EXECUTE PROCEDURE std_label_check()