Re: foreign key with where clause

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.