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

On 13 December 2010 10:30, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Seriously, real-world use cases such as Kevin's one seems to warrant
> that we are able to create a table withouth enforcing the FK. That's
> horrid, yes, that's needed, too. Maybe some operations would have to be
> instructed that the constraint ain't trustworthy but just declared to be
> so by the user?

Might I suggest that we call them "aspirational foreign keys", while
sticking with Simon's syntax?

Reasons:

1. It's suggestive of the lack of certainty about the referential
integrity of the underlying data - They're like a foreign key, but not
quite as good.
2. It's indicative that they may one day become actual foreign keys
through the use of something like the CHECK DATA utility. I'd favour
doing this with a separate DDL command.
3. It's suggestive that they aren't just syntactic sugar or an
expression of intent, as DB2's NOT ENFORCED FKs are, but rather that
they behave like Oracle's NOT ENFORCED FKs.
4. It's memorable, I think.

By the way, the DISABLE TRIGGER ALL method isn't equivalent to this.
Apart from hackishly depending on an implementation detail, it isn't
possible to prevent the big, up-front enforcement in the first place
when the FK is declared, because DISABLE TRIGGER ALL only disables
existing triggers. Perhaps, if and when this feature is implemented,
it will also be possible to use some explicit mechanism to disable and
re-enable an FK. However, that's secondary I think.

--
Regards,
Peter Geoghegan