PostgreSQL 外键条件检查约束 [英] PostgreSQL check constraint for foreign key condition
问题描述
我有一个用户表,例如:
I have a table of users eg:
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
);
作业可以分配给用户,但只能分配给超级用户.其他用户不能分配作业.
the jobs can be assigned to users, but only for superusers. other users cannot have jobs assigned.
所以我有一个表格,可以看到哪个作业分配给了哪个用户:
So I have a table whereby I see which job was assigned to which user:
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
的用户.
But I want to create a check constraint that the user_id
references a user that has user.superuser = True
.
这可能吗?还是有其他解决方案?
Is that possible? Or is there another solution?
推荐答案
这适用于 INSERTS:
This would work for 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 表的检查约束:
And then a check contraint on the user_has_job table:
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
因此,如果您允许更新用户,则需要在用户表上创建更新触发器,以防止在用户有工作时发生这种情况.
So if you allow updating users you need to create an update trigger on the users table to prevent that if the user has jobs.
这篇关于PostgreSQL 外键条件检查约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!