Re: URGENT: referential integrity problem

On Wed, 29 Jan 2003, 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 !
>
> My questions:
>
> How is it possible, that pg do not check the references by inserts?

It could be a bug, or an old bug if you've upgraded the machine (it
doesn't recheck the constraints in most version on dump/restore).

Without more information though, I don't think it's possible to speculate.

> 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).

Try something like:
select * from fktable where fcol1 is not null [and ...] and not exists
(select * from pktable where pktable.pcol1 = fktable.fcol1 [and ...]);

I think that'll find match unspecified cases. For match full, it'd miss
partially null fktable rows, but it doesn't sound like that's your
problem.