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 :).