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。

这是否可行？或者还有其他解决方案吗？

- microo8

---

我想要一个约束条件，如果我插入一行新数据，则检查 superuser=True。否则它不会保存新的数据行。 - microo8

Answer 1

这对于插入操作是可行的。

create or replace function is_superuser(int) returns boolean as $$
select superuser from "user" where id = $1;
$$ 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

所以如果你允许更新用户，你需要在用户表上创建一个更新触发器，以防止用户有作业时进行更新。

- Michael Legart

Answer 2

我能想到的唯一方法是在users表上添加一个对(id, superuser)的唯一约束，并通过“复制”superuser标志在user_has_job表中引用它。

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

-- as id is already unique there is no harm adding this additional 
-- unique constraint (from a business perspective)
alter table users add constraint uc_users unique (id, superuser);

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

create table user_has_job (
    user_id integer references users (id),
    -- we need a column in order to be able to reference the unique constraint in users
    -- the check constraint ensures we only reference superuser
    superuser boolean not null default true check (superuser), 
    job_id integer references job(id),
    constraint user_has_job_pk PRIMARY KEY (user_id, job_id),
    foreign key (user_id, superuser) references users (id, superuser)
);


insert into users 
 (id, name, superuser)
values 
  (1, 'arthur', false),
  (2, 'ford', true);

insert into job 
  (id, description)
values   
  (1, 'foo'),
  (2, 'bar');

由于default值，当插入user_has_job表时不需要指定superuser列。因此，以下插入操作可以正常工作：

insert into user_has_job 
  (user_id, job_id)
values
  (2, 1);

但是尝试将Arthur插入表中失败了：

insert into user_has_job 
  (user_id, job_id)
values
  (1, 1);

这也可以防止将ford转变为非超级用户。以下是更新内容：

update users 
  set superuser = false 
where id = 2;

出现错误，错误信息如下：

ERROR: 在表 "users" 上执行更新或删除操作违反了表 "user_has_job" 中的外键约束"user_has_job_user_id_fkey1"。
详情：键 (id, superuser)=(2, t) 仍被表 "user_has_job" 引用。

- user330315

Answer 3

创建一个从user表继承的单独的superuser表：

CREATE TABLE "user" (
    id serial PRIMARY KEY,
    name text NOT NULL,
);

CREATE TABLE superuser () INHERITS ("user");

user_has_job表可以引用superuser表：

CREATE TABLE user_has_job (
    user_id integer REFERENCES superuser (id),
    job_id integer REFERENCES job(id),
    PRIMARY KEY (user_id, job_id)
);

根据需要插入和删除，将用户在表格之间移动：

WITH promoted_user AS (
    DELETE FROM "user" WHERE id = 1 RETURNING *
) INSERT INTO superuser (id, name) SELECT id, name FROM promoted_user;

- Richard Simões

Answer 4

我不知道这是否是一个好的方法，但它似乎可以工作

    INSERT INTO user_has_job (user_id, job_id) VALUES (you_user_id, your_job_id)
    WHERE EXIST (
        SELECT * FROM user WHERE id=your_user_id AND superuser=true
    );

- thiout_p