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