Postgres Pro
Downloads
Home   >   mailing lists

Thread: foreign key with where clause

foreign key with where clause

From
Mark Lybarger
Date:
I have two tables that i want to link with a FK where the child table record is "active".

some googling shows that i could use a function and a check constraint on the function, but that only works for inserts, not updates on table b.  

create table a (int id, text name);
create table b (int id, boolean active);

alter table a add column b_id integer;
-- how to do this?
alter table a add foreign key (b_id) references b(id) where b.active == true

help :).

Re: foreign key with where clause

From
Branden Visser
Date:
My first instinct would be to look into triggers. In addition to an FK
a(b_id) -> b(id), you could have an insert and update trigger on
a(b_id) and b(active) to ensure the additional constraints.

On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger <mlybarger@gmail.com> wrote:
> I have two tables that i want to link with a FK where the child table record
> is "active".
>
> some googling shows that i could use a function and a check constraint on
> the function, but that only works for inserts, not updates on table b.
>
> create table a (int id, text name);
> create table b (int id, boolean active);
>
> alter table a add column b_id integer;
> -- how to do this?
> alter table a add foreign key (b_id) references b(id) where b.active == true
>
> help :).

Re: foreign key with where clause

From
Manuel Gómez
Date:
On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger <mlybarger@gmail.com> wrote:
> I have two tables that i want to link with a FK where the child table record
> is "active".
>
> some googling shows that i could use a function and a check constraint on
> the function, but that only works for inserts, not updates on table b.
>
> create table a (int id, text name);
> create table b (int id, boolean active);
>
> alter table a add column b_id integer;
> -- how to do this?
> alter table a add foreign key (b_id) references b(id) where b.active == true
>
> help :).

If you can afford the overhead, this works:

create table b (
  id int primary key,
  active boolean not null,
  unique (id, active)
);

create table a (
  id int primary key,
  name text,
  b_id int not null,
  b_active boolean not null check (b_active = true),
  foreign key (b_id, b_active) references b (id, active)
);

(The «= true» is redundant, of course)

Sadly, you need the extra redundant index on (id, active) despite
«active» being functionally dependent on «id», as foreign key
constraints require a unique index on the referenced table to the
exact set of columns that comprise the foreign key.  You also need the
extra column in the referencing table, and it will only ever contain
«true».

It’d be very nice if foreign key constraints were aware of functional
dependencies, and even nicer if they could be pointed at a partial
index.  This would allow a lot of complex integrity constraints to be
enforced with foreign key constraints with no overhead.  Not sure how
feasible such a feature would be.