Re: FKey not enforced resulting in broken Dump/Reload

On Fri, Jul 19, 2013 at 12:58 PM, Rod Taylor <rod(dot)taylor(at)gmail(dot)com> wrote:
> A poorly coded trigger on the referencing table has the ability to break
> foreign keys, and as a result create a database which cannot be dumped and
> reloaded.
>
> The BEFORE DELETE trigger accidentally does RETURN NEW, which suppresses the
> DELETE action by the foreign key trigger. This allows the record from the
> referenced table to be deleted and the record in the referencing table to
> remain in place.
>
> While I don't expect Pg to do what the coder meant, but it should throw an
> error and not leave foreign key'd data in an invalid state.

This is a known limitation of our foreign key machinery. It might
well be susceptible to improvement, but I wouldn't count on anyone
rewriting it in the near future.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company