Re: URGENT: referential integrity problem

pginfo wrote:
> Hi,
>
> I have 2 tables, in the first one I have field that points to the table
> key from the second.
>
> I the forst I have ~ 1M records and in the second 100K.
>
> I start one update over the first table on anoder field ( not on the
> reference field).
>
> After few min. pg drops with:
> ERROR: <unnamed> referential integrity violation - key referenced from
> a_table1 not found in a_table2 !
>

Hi Ivan,

I will try to help you with some common sense remarks because I am
Postgresql newbie, and you stated you need help quick.
If you are lucky a guru drops in.

> My questions:
>
> How is it possible, that pg do not check the references by inserts?
>

Maybe the constraint (FK) was activated when the tables were populated
already?
Is this possible?

> How can I check the db integrity for all tables at once ( I need to be
> sure, that do not exists any problems. It is production server).

I do not know how to check the whole database at once.
AAFAIK it isn't possible.

But if you want to check between 2 tables if the FK works, just make a
query like:

SELECT table1_FK_Field,bla,bla FROM table1 WHERE (table1_FK_Field NOT IN
(SELECT PK_Field FROM table2))

something like that should work.

I hope you solve the problem quickly.

Good luck and regards,
Erwin

> regards,
> ivan.