On 7/1/05, Andrus <noeetasoftspam@xxxxxxxxx> 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
