Re: Verifying Referential Integrity

Geisler, Jim wrote:

> So, as far as I know, PostgreSQL does not have any way of verifying
> the loss of referential integrity.
>
> Are there any recommended methods or utilities for checking
> referential integrity in a PostgreSQL database?
>

Of course, Tom Lane suggested I look at the pg_trigger table. I suppose
I'll have to do this from time to time. His good point on an old
database system (7.1.3) like what is included within Red Hat AS 2.1 is
as he wrote, "Each FK constraint should have three associated triggers
(two on the referencing table, one on the referenced table). You can
sort out which is which by looking at the tgargs field --- note how the
referencing and referenced table and field names are embedded in that. I
suspect that some of these triggers got dropped or disabled.

If you don't find all three triggers for some one constraint, the best
bet is to drop any remaining triggers from the set and then issue
ALTERTABLE ADD FOREIGN KEY to re-make a consistent trigger set."

I did what he suggested and then re-created the offending table and
altered the other offending table. Now, my database has working
referential integrity between the two tables involved.

Jim Apsey