PostgreSQL check constraint for foreign key condition

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 1postgres=# insert into "user" (name,superuser) values ('name2',true);INSERT 0 1postgres=# insert into job (description) values ('test');INSERT 0 1postgres=# 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.

The only way I can think of is to add a unique constraint on (id, superuser) to the users table and reference that from the user_has_job table by "duplicating" the superuser flag there:

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

Due to the default value, you don't have to specify the superuser column when inserting into the user_has_job table. So the following insert works:

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

But trying to insert arthur into the table fails:

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

This also prevents turning ford into a non-superuser. The following update:

update users   set superuser = false where id = 2;

fails with the error

ERROR: update or delete on table "users" violates foreign key constraint "user_has_job_user_id_fkey1" on table "user_has_job"
Detail: Key (id, superuser)=(2, t) is still referenced from table "user_has_job".

Create a separate superuser table that inherits from the user table:

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

The user_has_job table can then reference the superuser table:

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

Move users around between the tables as needed by inserting and deleting:

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