PostgreSQL: How to constrain more than just existence on foreign key?

Question

In PostgreSQL, what's the simplest way to enforce more than just existence on a foreign key?

For example, given the following tables:

create table "bar"
    (
    bar_id serial primary key,
    status boolean not null
    )

create table "foo"
    (
    foo_id serial primary key,
    bar_id integer references "bar"
    )

How could foo.bar_id be constrained to only rows of bar where status is true?

I can imagine how to do it with trigger functions, but it seems like I'd need several (insert, update on foo; update, delete on bar) so I'd like to know if there's a more convenient method, perhaps purely using constraints.

Answer 1

The hardcore way would be to have a intermediate/subcategory table sitting between foo and bar, containing the bar values whose status is true. The foreign key on foo would point to the intermediate table, but it would require maintainance on both sides in the event that bar values are added or status changes to false...

The alternative is to use a trigger, because a foreign key has no means of filtering.

PostgreSQL doesn't have materialized views; I'm not sure if it would allow you to specify a foreign key reference to a view, but it would mean an issue in the event that a bar status changes to false...

Answer 2

This is one of the many situations in which standard SQL relational integrity falls short of meeting real world requirements. I wish, for instance, that one could create a foreign key against an index rather than an entire column; postgresql offers partial indexes, and that would solve your problem.

Failing that, you would need to divide bar into two tables bar_true and bar_false, each without the status field. Establish your FK against bar_true. You could then create a view UNIONing bar_true and bar_false together to get the complete bar set (although you'd have to take care with the primary keys).

Answer 3

You could make bar.status part of the primary key of the bar table. Then you put status on the foo table as part of the FK to bar. Also put a constraint that foo.status must equal true. It's kind of a hack, but depending on the real world context, it might make sense.

Answer 4

Seems if you used triggers you could control only letting records into foo that have a bar status of true and you could also put a trigger on bar that if a status went to false it would remove the record in foo.