On Mon, Dec 2, 2013 at 10:04 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> The only way I have thought of as an alternative to this proposal is to use
> a partitioned table with different FK constraints for each child. That's
> certainly doable, but not without a deal of work, and even then you'd be
> giving up certain things, such as guaranteeing the uniqueness of the object
> key, at least without a lot more work.
>
> You can think of it this way: we currently enforce FK constraints except
> when the value being constrained is NULL (or part of it is NULL in the MATCH
> SIMPLE case). This is really a user-defined extension of the exception
> condition. I have at least one case where I could have used this feature and
> saved a significant amount of work. We wanted to apply FK constraints to a
> very large table, but grandfather in certain cases that didn't meet the
> constraint. That could have been done very simply using this feature.
I also like this feature. It would be really neat if a FOREIGN KEY
constraint with a WHERE clause could use a *partial* index on the
foreign table provided that the index would be guaranteed to be predOK
for all versions of the foreign key checking query. That might be
hard to implement, though.
Whether that works or not, it seems to me that a good deal of thought
will need to be given to what dependencies get created when creating a
constraint of this type.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company