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

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> 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 can point to a use case that has proven useful...

Slony-I deactivates indices during the subscription process, because it
is enormously more efficient to load the data into the tables
sans-indices, and then re-index afterwards.

The same would apply for FK constraints.

I observe that the deactivation of indices is the sole remaining feature
in Slony-I that still requires catalog access in a "corruptive" sense.
(With the caveat that this corruption is now only a temporary one; the
indexes are returned into play before the subscription process
finishes.)

That would be eliminated by adding in:
"ALTER TABLE ... DISABLE INDEX ..."
"ALTER TABLE ... ENABLE INDEX ..."

For similar to apply to FK constraints would involve similar logic.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/rdbms.html
"The code should be beaten into submission" -- Arthur Norman