Postgres-eindeutige Einschränkung für Zeilengruppen

Question

Ich benutze postgresql 10.12

Ich habe Entitäten beschriftet. Einige sind Standard, andere nicht. Standardentitäten werden von allen Benutzern gemeinsam genutzt, während nicht Standardentitäten im Besitz des Benutzers sind. Nehmen wir also an, ich habe eine Tabelle Entitymit einer Textspalte Labelund eine Spalte, user_iddie für Standardentitäten null ist.

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

Hier ist meine Einschränkung: Zwei nicht standardmäßige Entitäten, die verschiedenen Benutzern gehören, können dieselbe Bezeichnung haben. Standardentitätsbezeichnungen sind eindeutig, und Entitäten eines bestimmten Benutzers haben eindeutige Bezeichnungen. Der schwierige Teil ist: Eine Bezeichnung muss innerhalb einer Gruppe von Standardentitäten + Entitäten eines bestimmten Benutzers eindeutig sein.

Ich verwende sqlAlchemy. Hier sind die Einschränkungen, die ich bisher vorgenommen habe:

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

Mein Problem mit dieser Einschränkung ist, dass ich nicht garantiere, dass eine Benutzerentität keine Standardentitätsbezeichnung hat.

Beispiel:

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

Dies ist eine gültige Tabelle. Ich mag sicherstellen , dass es nicht mehr möglich ist ein Unternehmen mit dem Label zu erstellen std_ent, dass Benutzer 42 nicht ein anderes Unternehmen mit dem Label erstellen user_ent_1oder user_ent_2und der Benutzer 43 nicht ein anderes Unternehmen mit dem Label erstellen user_ent_1.

Mit meinen aktuellen Einschränkungen ist es den Benutzern 42 und 43 weiterhin möglich, eine Entität mit Beschriftung zu erstellen std_ent, was ich korrigieren möchte.

Irgendeine Idee?

Answer 1

Wenn Ihre eindeutigen Einschränkungen verhindern, dass Benutzer doppelte Beschriftungen für ihre eigenen "Benutzerentitäten" eingeben, können Sie verhindern, dass sie die Beschriftung einer "Standardentität" eingeben, indem Sie einen Trigger hinzufügen.

Sie erstellen eine Funktion…

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

… Und befestigen Sie es dann als Auslöser an der Tabelle

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