Batasan unik Postgres pada kelompok baris

Question

Saya menggunakan postgresql 10.12

Saya memiliki entitas berlabel. Beberapa standar, beberapa tidak. Entitas standar dibagikan di antara semua pengguna, sedangkan entitas standar bukan milik pengguna. Jadi katakanlah saya memiliki tabel Entitydengan kolom teks Label, dan kolom user_idyang nol untuk entitas standar.

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

Inilah kendala saya: dua entitas bukan standar milik pengguna yang berbeda dapat memiliki label yang sama. Label entitas standar itu unik, dan entitas pengguna tertentu memiliki label unik. Bagian tersulitnya adalah: label harus unik dalam grup entitas standar + entitas pengguna tertentu.

Saya menggunakan sqlAlchemy, berikut adalah batasan yang saya buat sejauh ini:

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

Masalah saya dengan batasan ini adalah saya tidak menjamin bahwa entitas pengguna tidak akan memiliki label entitas standar.

Contoh:

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

Ini adalah tabel yang valid. Saya ingin memastikan bahwa tidak mungkin lagi membuat entitas dengan label std_ent, pengguna 42 itu tidak dapat membuat entitas lain dengan label user_ent_1atau user_ent_2dan pengguna itu 43 tidak dapat membuat entitas lain dengan label user_ent_1.

Dengan batasan saya saat ini, masih memungkinkan bagi pengguna 42 dan 43 untuk membuat entitas dengan label std_ent, yang ingin saya perbaiki.

Ada ide?

Answer 1

Jika batasan unik Anda melakukan tugasnya mencegah pengguna memasukkan label duplikat untuk "entitas pengguna" mereka sendiri, Anda dapat mencegah mereka memasukkan label "entitas standar" dengan menambahkan pemicu.

Anda membuat fungsi…

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

… Lalu pasangkan sebagai pemicu ke tabel

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