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

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.

--
Regards,
Peter Geoghegan