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

On Sun, Dec 12, 2010 at 4:49 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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

MySQL does in fact have this feature and it is used by mysqldump. This
feature is very useful.

--
Rob Wultsch
wultsch(at)gmail(dot)com