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

On Mon, Dec 13, 2010 at 05:15:29PM +0000, Peter Geoghegan wrote:
> On 13 December 2010 16:08, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> 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
> >
> > I'm less sure about this part.  I think there should be a DDL
> > statement to validate the foreign key.  The "return the problem" rows
> > behavior could be done some other way, or just left to the user to
> > write their own query.
>
> +1. I think that a DDL statement is more appropriate, because it makes
> the process sort of symmetrical.
>
> Perhaps we could error on the first FK violation found, and give a
> "value 'foo' not present in table bar". It ought to not matter that
> there could be a lot of violations, because they will be exceptional
> if you're using the feature as intended - presumably, you're going to
> want to comb through the data to find out exactly what has gone wrong
> for each violation. On the off chance that it actually is a problem,
> the user can go ahead and write their own query, like Robert
> suggested.

Perhaps the errhint could suggest a query. All the information needed
for it would be available :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate