行のグループに対するPostgresの一意性制約

Question

postgresql10.12を使用しています

エンティティにラベルを付けました。標準のものもあれば、そうでないものもあります。標準エンティティはすべてのユーザー間で共有されますが、標準エンティティはユーザー所有ではありません。したがってEntity、テキスト列Labelと、user_id標準エンティティの場合はnullの列を持つテーブルがあるとします。

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

これが私の制約です。異なるユーザーに属する2つの標準ではないエンティティが同じラベルを持つことができます。標準エンティティラベルは一意であり、特定のユーザーのエンティティには一意のラベルがあります。難しいのは、ラベルは標準エンティティのグループ+特定のユーザーのエンティティ内で一意である必要があるということです。

私はsqlAlchemyを使用しています。これまでに行った制約は次のとおりです。

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

この制約に関する私の問題は、ユーザーエンティティに標準のエンティティラベルがないことを保証しないことです。

例：

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

これは有効なテーブルです。ラベル付きのエンティティを作成std_entできないこと、ユーザー42がラベル付きの別のエンティティを作成できないことuser_ent_1、user_ent_2およびユーザー43がラベル付きの別のエンティティを作成できないことを確認したいと思いuser_ent_1ます。

私の現在の制約では、ユーザー42と43がラベル付きのエンティティを作成することは可能ですがstd_ent、これを修正したいと思います。

何か案が？

Answer 1

独自の制約が、ユーザーが自分の「ユーザーエンティティ」に重複するラベルを入力できないようにする役割を果たしている場合は、トリガーを追加することで、ユーザーが「標準エンティティ」のラベルを入力できないようにすることができます。

関数を作成します…

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

…そしてそれをトリガーとしてテーブルに取り付けます

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