Re: Which record causes referential integrity violation on delete

On 7/1/05, Andrus <noeetasoftspam(at)online(dot)ee> wrote:
> In Postgres 8 I tried commad
>
> 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 ?

Well, I am not sure, but information you want may be contained in
information_schema.key_column_usage and
information_schema.referential_constraints

From psql client, simply use "\d" command.

> Why Postgres does not report primary key value in error message ?

My guess it is because it gives unnecesary complication. And maybe
there would be also some performance hit, but I am not sure of the
latter.

> I it is not possible to add more information to error I need function which
> takes 3 arguments:
>
> table name ('customer')
> field name ('id')
> field value (123)
>
> and returns the name of the table and primary key value which blocks
> record deletion.

Hmm, let's try to do it another way. You know that the constraint causing
the problem was "invoice_customer_fkey".

So you need to:
SELECT unique_constraint_schema,unique_constraint_name FROM
information_schema.referential_constraints WHERE constraint_schema =
'public' AND constraint_name = 'invoice_customer_fkey';

Supposedly, it will return 'public', 'invoice_pkey' values.

This gives you an information about which constraint 'really holds' your delete.
Then do:
SELECT table_schema,table_name,column_name FROM
information_schema.key_column_usage WHERE constraint_schema = 'public'
AND constraint_name = 'invoice_pkey' ORDER BY ordinal_position;

This will give, for example:

'public' | 'invoice' | 'year'
'public' | 'invoice' | 'month'
'public' | 'invoice' | 'id'

(assuming invoices are identified by date and this month's order id.

So now you know that to get that primary key that blocks you from
removing date is:

SELECT year,month,id FROM invoice WHERE <foreign key columns>;

Regards,
Dawid

PS: It is possible to make steps similar to these using PL/pgSQL, its
not that difficult actually. But I would tend to thing that it would be better
if the client (the application) would know the data and was able to handle
such situations. I.e. if there is a FK violation on customers, to present
the user with list of undeleted customers invoices and ask her if it should
be removed. Handling it all "behind the scenes" in a backend may not
be the best solution.