PostgreSQL检查外键条件的约束

Question

我有一个用户表，例如：

create table "user" (
    id serial primary key,
    name text not null,
    superuser boolean not null default false
);

和一张有工作的桌子：

create table job (
    id serial primary key,
    description text
);

可以将作业分配给用户，但只能分配给超级用户。其他用户无法分配作业。

因此，我有一个表格，通过它可以查看将哪个作业分配给了哪个用户：

create table user_has_job (
    user_id integer references "user"(id),
    job_id integer references job(id),
    constraint user_has_job_pk PRIMARY KEY (user_id, job_id)
);

但是我想创建一个检查约束，该约束user_id引用具有的用户user.superuser = True。

那可能吗？还是有其他解决方案？

Answer 1

这将适用于INSERTS：

create or replace function is_superuser(int) returns boolean as $$
select exists (
    select 1
    from "user"
    where id   = $1
      and superuser = true
);
$$ language sql;

然后在user_has_job表上检查约束：

create table user_has_job (
    user_id integer references "user"(id),
    job_id integer references job(id),
    constraint user_has_job_pk PRIMARY KEY (user_id, job_id),
    constraint chk_is_superuser check (is_superuser(user_id))
);

适用于插入物：

postgres=# insert into "user" (name,superuser) values ('name1',false);
INSERT 0 1
postgres=# insert into "user" (name,superuser) values ('name2',true);
INSERT 0 1

postgres=# insert into job (description) values ('test');
INSERT 0 1
postgres=# insert into user_has_job (user_id,job_id) values (1,1);
ERROR:  new row for relation "user_has_job" violates check constraint "chk_is_superuser"
DETAIL:  Failing row contains (1, 1).
postgres=# insert into user_has_job (user_id,job_id) values (2,1);
INSERT 0 1

但是，这是可能的：

postgres=# update "user" set superuser=false;
UPDATE 2

因此，如果允许更新用户，则需要在用户表上创建一个更新触发器，以防止该用户有工作。