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

On Sun, Dec 12, 2010 at 7:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> ...  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.
>
> There may well be a case to be made for doing this on grounds of
> practical usefulness.  I'm just voicing extreme skepticism that it can
> be supported by reference to the standard.

Dunno, I haven't read it either. But it does seem like the natural
interpretation of "NOT ENFORCED".

> Personally I'd prefer to see us look into whether we couldn't arrange
> for low-impact establishment of a verified FK relationship, analogous to
> CREATE INDEX CONCURRENTLY.  We don't let people just arbitrarily claim
> that a uniqueness condition exists, and ISTM that if we can handle that
> case we probably ought to be able to handle FK checking similarly.

That'd be useful, too, but I don't think it would remove the use case
for skipping the check altogether.

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