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

On Mon, 2010-12-13 at 11:54 +0000, Peter Geoghegan wrote:
> On 13 December 2010 10:30, Dimitri Fontaine <dimitri@2ndquadrant.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?

Just checking what we are saying:

1. 
(a) ALTER TABLE ... ADD FOREIGN KEY ... NOT VALIDATED INITIALLY;
will add a FK but NOT run the check - we mark it as "check pending".
Lock held: ShareRowExclusiveLock

(b) Every new change to the table has the FK enforced - the triggers are
fully enabled and active. (That could mean we update a row and have the
update fail because of a FK violation.)

2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock

Note that 1 & 2 together are the equivalent of ADD FK CONCURRENTLY,
except that step 2 more usefully tells you which rows fail.

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