On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger <mlybarger@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
