Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED

Tom Lane  wrote:
> I seem to recall pointing out upthread that the FK check triggers
> are designed on the assumption that the constraint does hold
> currently.  Has any analysis been done on exactly how badly they'll
> fail when it doesn't hold? I remain unconvinced that this behavior
> is desirable.
I saw your upstream comment, and it took a quick look at it.  On the
face of it, I couldn't see where checking that the parent exists on
the insert of a child would be broken by the existence of other
orphan children, nor could I see where checking for the absence of
children on the delete of a parent would be broken by orphan children
not related to the parent.  With other things on my plate I didn't
have time to do a rigorous check, but it was enough to make me wonder
what you think depends on existing consistency or what could break. 
Even a vague hint of what sort of thing you think might go wrong
might help people find problem code, if it actually exists.
Now, I understand that a broken index, like one based on a function
declared immutable which really isn't, could cause problems, but that
seems orthogonal.
-Kevin