the check constraint ensures we only reference superuser ![]() we need a column in order to be able to reference the unique constraint in users unique constraint (from a business perspective)Īlter table users add constraint uc_users unique (id, superuser) as id is already unique there is no harm adding this additional 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 ( 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. However this is possible: postgres=# update "user" set superuser=false Postgres=# insert into user_has_job (user_id,job_id) values (2,1) Postgres=# insert into user_has_job (user_id,job_id) values (1,1) ĮRROR: new row for relation "user_has_job" violates check constraint "chk_is_superuser" ![]() Postgres=# insert into job (description) values ('test') Postgres=# insert into "user" (name,superuser) values ('name2',true) Works for inserts: postgres=# insert into "user" (name,superuser) values ('name1',false) ![]() This would work for INSERTS: create or replace function is_superuser(int) returns boolean as $$Īnd then a check contraint on the user_has_job table: create table user_has_job (Ĭonstraint user_has_job_pk PRIMARY KEY (user_id, job_id),Ĭonstraint chk_is_superuser check (is_superuser(user_id))
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |