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?
See Question&Answers more detail:os