sql - 如何在 PostgreSQL 中创建条件外键？

Question

我有下表

create table if not exists pgroup (
    id uuid primary key default gen_random_uuid(),
    label varchar not null,
    is_role boolean default false
);

我想创建一个如下所示的表:

create table if not exists grouprole (
    groupId uuid not null references pgroup(id) `where is_role = false`,
    roleId uuid not null references pgroup(id) `where is_role = true`,
    primary key (groupId, roleId)
);

这个想法是，如果一个是角色而另一个不是，则两个 pgroup 可以处于组角色关系中。

我的目标是在插入时执行检查以确保这一点。

编辑:

我无法将 pgroup 拆分为两个不同的表，因为其他表引用它并且不关心 is_role 标志。

Answer 1

尝试在CHECK约束中使用辅助函数:

create table if not exists pgroup (
    id int primary key,
    label varchar not null,
    is_role boolean default false
);

create table if not exists grouprole (
    groupId int not null references pgroup(id),
    roleId int not null references pgroup(id),
    primary key (groupId, roleId)
);

CREATE FUNCTION check_pgroup(p_id int,p_is_role boolean) RETURNS int AS $$
    SELECT id
    FROM pgroup
    WHERE id=p_id
      AND is_role=p_is_role
$$ LANGUAGE SQL;

alter table grouprole add check(check_pgroup(groupId,false) is not null);
alter table grouprole add check(check_pgroup(roleId,true) is not null);

测试:

INSERT INTO pgroup(id,label,is_role)VALUES(1,'1',true);
INSERT INTO pgroup(id,label,is_role)VALUES(2,'2',false);

INSERT INTO grouprole(groupId,roleId)VALUES(1,2); -- Error
INSERT INTO grouprole(groupId,roleId)VALUES(2,1); -- OK

您还可以创建对 pggroup 的交叉检查，以防止在 is_role 中设置错误值:

CREATE FUNCTION check_pgroup_is_role(p_id int,p_is_role boolean) RETURNS boolean AS $$
  SELECT true is_exists
  FROM grouprole
  WHERE ((p_is_role=true AND groupId=p_id) OR (p_is_role=false AND roleId=p_id))
$$ LANGUAGE SQL;

ALTER TABLE pgroup ADD CHECK(check_pgroup_is_role(id,is_role) IS NULL);

测试:

UPDATE pgroup SET is_role=false; -- Error

INSERT INTO pgroup(id,label,is_role)VALUES(3,'3',true); -- OK
UPDATE pgroup SET is_role=false WHERE id=3; -- OK