행 그룹에 대한 Postgres 고유 제한

Question

postgresql 10.12를 사용하고 있습니다.

나는 엔티티에 라벨을 붙였습니다. 일부는 표준이고 일부는 그렇지 않습니다. 표준 엔티티는 모든 사용자간에 공유되지만 표준 엔티티는 사용자 소유가 아닙니다. 따라서 Entitytext column 이있는 테이블 과 표준 엔터티의 경우 null 인 Label열 user_id이 있다고 가정 해 보겠습니다 .

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

내 제약은 다음과 같습니다. 서로 다른 사용자에 속하는 두 개의 표준 엔티티가 동일한 레이블을 가질 수 있습니다. 표준 엔터티 레이블은 고유하며 특정 사용자의 엔터티에는 고유 한 레이블이 있습니다. 어려운 부분은 레이블이 표준 엔터티 그룹 + 주어진 사용자 엔터티 내에서 고유해야한다는 것입니다.

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()