PostgreSQL检查外键条件的约束 [英] PostgreSQL check constraint for foreign key condition

问题描述

我有一个用户表，例如：

pre codereate table table user $ b $ id serial key，
名称文本不为空，
超级用户布尔值非空默认false
）;

以及包含作业的表格：

  create table job（
 id serial primary key，
 description text 
）; 
  

可以将作业分配给用户，但仅限于超级用户。其他用户不能分配任务。

所以我有一个表格，我可以看到哪个工作分配给了哪个用户：

  create table user_has_job（
 user_id整数引用user（id），
 job_id整数引用作业（id），$ b $约束user_has_job_pk PRIMARY KEY（user_id，job_id）
）; 
  

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

这可能吗？或者还有另一种解决方案？

解决方案

这对于INSERTS是有效的：

 创建或替换函数is_superuser（int）返回布尔值为$$ 
 select exists（
从user中选择
 
 where id = $ 1 
和superuser = true 
）; 
 $$语言sql; 
  

然后在user_has_job表中检查一下：

pre $ create table user_has_job（
user_id整数引用user（id），
job_id整数引用job（id），
约束user_has_job_pk PRIMARY KEY（user_id，job_id），
约束chk_is_superuser检查（is_superuser（user_id））
）;


适用于插入：

  postgres =＃insert intouser（name，superuser）values（'name1'，false）; 
 INSERT 0 1 
 postgres =＃insert intouser（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）; 
错误：关系user_has_job的新行违反检​​查约束chk_is_superuser
详细信息：失败的行包含（1，1）。 
 postgres =＃insert into user_has_job（user_id，job_id）values（2,1）; 
 INSERT 0 1 
  

然而这是可能的：

  postgres =＃updateuserset superuser = false; 
 UPDATE 2 
  

所以如果你允许更新用户，你需要在用户表，以防止用户有工作。

I have a table of users eg:

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

and a table with jobs:

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

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?

解决方案

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;

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

Works for inserts:

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

However this is possible:

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屋！