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

On Sun, 2010-12-12 at 19:07 -0500, Tom Lane 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.
>
> 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.

I think we should do *both* things. Sometimes you already know the check
will pass, sometimes you don't. In particular, reloading data from
another source where you knew the checks passed. Enforcing re-checking
in that case reduces data availability.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services