Vincolo unico di Postgres sui gruppi di righe

Question

Sto usando postgresql 10.12

Ho etichettato entità. Alcuni sono standard, altri no. Le entità standard sono condivise tra tutti gli utenti, mentre le entità non standard sono di proprietà dell'utente. Quindi diciamo che ho una tabella Entitycon una colonna di testo Labele una colonna user_idche è nulla per le entità standard.

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

Ecco il mio vincolo: due entità non standard appartenenti a utenti diversi possono avere la stessa etichetta. Le etichette delle entità standard sono uniche e le entità di un determinato utente hanno etichette univoche. La parte difficile è: un'etichetta deve essere univoca all'interno di un gruppo di entità standard + entità di un dato utente.

Sto usando sqlAlchemy, ecco i vincoli che ho fatto finora:

__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)),
    ),
)

Il mio problema con questo vincolo è che non garantisco che un'entità utente non avrà un'etichetta di entità standard.

Esempio:

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

Questa è una tabella valida. Voglio assicurarmi che non sia più possibile creare un'entità con etichetta std_ent, che l'utente 42 non possa creare un'altra entità con etichetta user_ent_1o user_ent_2e che l'utente 43 non possa creare un'altra entità con etichetta user_ent_1.

Con i miei attuali vincoli, è ancora possibile per gli utenti 42 e 43 creare un'entità con etichetta std_ent, che è ciò che voglio correggere.

Qualche idea?

Answer 1

Se i tuoi vincoli univoci stanno facendo il loro lavoro per impedire agli utenti di inserire etichette duplicate per le loro "entità utente", puoi impedire loro di inserire l'etichetta di una "entità standard" aggiungendo un trigger.

Crei una funzione ...

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$
;

... e poi attaccalo come trigger al tavolo

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