CODEWITHSUNDEEP
sqlpostgresql
BlueMagma
BlueMagma

Reputation: 2505

How to create a Conditional foreign key in PostgreSQL?

I have the following table

create table if not exists pgroup (
    id uuid primary key default gen_random_uuid(),
    label varchar not null,
    is_role boolean default false
);

I want to create a table like the following :

create table if not exists grouprole (
    groupId uuid not null references pgroup(id) `where is_role = false`,
    roleId uuid not null references pgroup(id) `where is_role = true`,
    primary key (groupId, roleId)
);

the idea is that two pgroup can be in a grouprole relationship if one is a role and the other is not.

My goal is that at INSERT time a check will be performed to ensure that.

EDIT:

I cannot split pgroup in two different tables because other tables references it and don't care for the is_role flag.

Upvotes: 2

Views: 1829

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try to use an auxiliary function in CHECK constraint:

create table if not exists pgroup (
    id int primary key,
    label varchar not null,
    is_role boolean default false
);

create table if not exists grouprole (
    groupId int not null references pgroup(id),
    roleId int not null references pgroup(id),
    primary key (groupId, roleId)
);

CREATE FUNCTION check_pgroup(p_id int,p_is_role boolean) RETURNS int AS $$
    SELECT id
    FROM pgroup
    WHERE id=p_id
      AND is_role=p_is_role
$$ LANGUAGE SQL;

alter table grouprole add check(check_pgroup(groupId,false) is not null);
alter table grouprole add check(check_pgroup(roleId,true) is not null);

Test:

INSERT INTO pgroup(id,label,is_role)VALUES(1,'1',true);
INSERT INTO pgroup(id,label,is_role)VALUES(2,'2',false);

INSERT INTO grouprole(groupId,roleId)VALUES(1,2); -- Error
INSERT INTO grouprole(groupId,roleId)VALUES(2,1); -- OK

You also can create a cross-check into pggroup to prevent to set a bad value into is_role:

CREATE FUNCTION check_pgroup_is_role(p_id int,p_is_role boolean) RETURNS boolean AS $$
  SELECT true is_exists
  FROM grouprole
  WHERE ((p_is_role=true AND groupId=p_id) OR (p_is_role=false AND roleId=p_id))
$$ LANGUAGE SQL;

ALTER TABLE pgroup ADD CHECK(check_pgroup_is_role(id,is_role) IS NULL);

Test:

UPDATE pgroup SET is_role=false; -- Error

INSERT INTO pgroup(id,label,is_role)VALUES(3,'3',true); -- OK
UPDATE pgroup SET is_role=false WHERE id=3; -- OK

Upvotes: 5

Related Questions