Re: URGENT: referential integrity problem

Stephan Szabo wrote:

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

It is possible.The project started on pg 7.2.1.
But we executed many times dump/restore to migrate to the new versions.

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

  I wrote the sql for checking this table ( with this sql code I fond the
problem),
but I am not sure for all other tables (in case pg do not check the integrity
by dump/restore)
and I will to write script ot commandfor checking all my tables and
references.

regards,
ivan.