Thread: Which record causes referential integrity violation on delete
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 ?
Why Postgres does not report primary key value in error message ?
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.
I looked to the ODBC log, but this does'nt contain more information
about error !?
Andrus.
"Andrus" <noeetasoftspam@online.ee> writes:
> 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 ?
There is no generic way to do that, because the question makes an
assumption that isn't generic; in fact two of them. One, that there
is only one row of invoice referencing this customer row; and two,
that invoice even has a primary key, which is certainly not implied
by the existence of an FK reference to customer.
If your intention is to perform some generic action like deleting all
the referencing rows, you may well find that marking the foreign key
reference ON DELETE CASCADE (or one of the other available options)
would solve the problem without needing to add any client-side logic.
regards, tom lane
On 7/1/05, Andrus <noeetasoftspam@online.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.
On 7/2/05, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On 7/1/05, Andrus <noeetasoftspam@online.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
>
[,,,]
I forgot to add, this is of course a "simplistic" approach which:
1. may be simply wrong
2. assumes data is available to user in nformation_schema (I guess the
information schema lists only data owned by user; yet I am not sure
about that).
3. assumes foreign keys have really simple set up (no FKs depending on
FKs depending on FKs...)
4. and so on.
In other words, best approach would be "know thy schema" in the
application. ;)))
Regards,
Dawid
On Friday 01 July 2005 19:49, you wrote: > In Postgres 8 I tried commad > > DELETE FROM customer WHERE id=123 (snip) > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend Automatically answered?! :-) explain analyze DELETE FROM customer WHERE id=123 Martin
> I forgot to add, this is of course a "simplistic" approach which: > 1. may be simply wrong > 2. assumes data is available to user in nformation_schema (I guess the > information schema lists only data owned by user; yet I am not sure > about that). > 3. assumes foreign keys have really simple set up (no FKs depending on > FKs depending on FKs...) > In other words, best approach would be "know thy schema" in the > application. ;))) Tom and Dawid, I have 60 foreign key tables with ON DELETE RESTRICT constraint. I have primary key column in all of my tables. I want that in case of deletion error immediate parent table name and one primary key which prevents deletion is displayed to user. So user can correct bad invoice manually, try next deletion, proceed with next stopper invoice and so on ... My application runs as postgres super-user, so it can access all data from information schema. So it seems that it is reasonable to create generic routine for this. Unfortunately I do'nt have enough experience to create such kind of routine even if using sample SELECT statements written by Dawid. I have seen similar question asked several times in this newsgroup. This seems to be a common requirement. Isn't there really some ready made generic stored procedure which I can use ? If delete error occurs, Postgres knows the ctid of the restricting record. Why this ctid cannot be retrieved by ODBC client ? Andrus.
> On Friday 01 July 2005 19:49, you wrote: >> In Postgres 8 I tried commad >> >> DELETE FROM customer WHERE id=123 > > (snip) > >> ---------------------------(end of broadcast)--------------------------- >> TIP 8: explain analyze is your friend > > > Automatically answered?! :-) > > explain analyze DELETE FROM customer WHERE id=123 Martin, I tried the command explain analyze command but it produces exactly the same error message about referential integrity violation as DELETE FROM customer WHERE id=123 How this command can help me ? Andrus.
-----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@turnstep.com
PGP Key: 0x14964AC8 200507101001
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFC0SxyvJuQZxSWSsgRAmVnAJ0YCETKAbNxA6BvsSsXhe34VJ0p+QCgvUmE
/A8zSHv6a3XMH5hLvrulfDw=
=L90Q
-----END PGP SIGNATURE-----