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

On Sun, Dec 12, 2010 at 6:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote:
>>> Huh?  It allows you to postpone the check until commit.  That's far from
>>> not enforcing it.
>
>> This clearly implies that un-enforced constraints are not checked at
>> commit.
>
> [ shrug... ]  I can't argue with you about what may or may not be in an
> unpublished draft of an unratified version of the standard, since I
> don't have a copy.  But allow me to harbor doubts that they really
> intend to allow someone to force a constraint to be considered valid
> without any verification.  This proposal strikes me as something mysql
> would do, not the standards committee.  (In particular, can a constraint
> go from not-enforced to enforced state without getting checked at that
> time?)
>
> Even if you're reading the draft correctly, and the wording makes it
> into a released standard, the implementation you propose would break our
> code.  The incremental FK checks are designed on the assumption that the
> constraint condition held before; they aren't likely to behave very
> sanely if the data is bad.  I'd want to see a whole lot more analysis of
> the resulting behavior before even considering an idea like this.

Wow, you've managed to bash Simon, MySQL, and the SQL standards
committee all in one email.

I'm not going to argue that careful analysis isn't needed before doing
something like this - and, in particular, if we ever get inner-join
removal, which I'm still hoping to do at some point, a foreign key
that isn't actually guaranteed to be valid might result in queries
returning different answers depending on whether or not a join is
removed. I guess we'd have to define that as the user's problem for
alleging a foreign-key relationship that doesn't truly exist. On the
other hand, there's clearly also a use case for this behavior. If a
bulk load of prevalidated data forces an expensive revalidation of
constraints that are already known to hold, there's a real chance the
DBA will be backed into a corner where he simply has no choice but to
not use foreign keys, even though he might really want to validate the
foreign-key relationships on a going-forward basis.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company