Re: Which record causes referential integrity violation on delete

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

"Andrus" wrote:

> DELETE FROM customer WHERE id=123
>
> but got an error
>
> ERROR: update or delete on "customer" violates foreign key constraint
> "invoice_customer_fkey" on "invoice"'

> How to determine the primary key of invoice table which causes this error
> in generic way ?

> table name ('customer')
> field name ('id')
> field value (123)

There probably is no easy generic way. but you could certainly use a standard
naming scheme for your foreign key constraints and that would at least give
you two pieces of information: the "table name" and the "field name." The
field value can be derived in most cases from what you passed to the query.
You can put as much info as you want into the name, for example:

Constraint name = FK|ptable|primarykey|ftable|fkey

(I like the pipes as opposed to underscores as the former are less likely to
be used in table names.)

ALTER TABLE customer ADD CONSTRAINT "FK|invoice|id|customer|invkey"
FOREIGN KEY (invkey) REFERENCES invoice(id) ON DELETE RESTRICT;

You can invent your own system of course, but that's one simple way to
keep things sorted.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200507101001
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFC0SxyvJuQZxSWSsgRAmVnAJ0YCETKAbNxA6BvsSsXhe34VJ0p+QCgvUmE
/A8zSHv6a3XMH5hLvrulfDw=
=L90Q
-----END PGP SIGNATURE-----