Re: Combination of Triggers and self-FKs produces inconsistent data

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Tom Lane wrote:
>> This isn't a bug. If you create triggers that prevent the RI actions
>> from being taken, it's your own problem.

> Huh? Since when was it OK by us to have data which violates a declared
> FK under *any* circumstances?

You can't have your cake and eat it too, Josh. If we make the RI
mechanism operate at a level underneath triggers, then we'll lose all
sorts of useful capability that people are depending on. A couple of
examples:

* the ability to log table changes caused by RI cascades

* the ability to maintain row update timestamps when the update is
caused by an RI cascade

> Where in our docs does it say that
> Foreign Keys are not enforced if the table has triggers on it?

It doesn't say that, because it isn't true. What is true is that if you
make a trigger that prevents updates from happening, it breaks RI
updates as well as directly-user-initiated updates. Either way, you're
going to need to fix the trigger.

regards, tom lane