Re: Verifying Referential Integrity

Tom Lane wrote:

>"Geisler, Jim" <jgeisler(at)vocollect(dot)com> writes:
>
>
>>So, as far as I know, PostgreSQL does not have any way of verifying the loss
>>of referential integrity.
>>
>>
>
>What are you trying to accomplish here, and in what PG version?
>
>Are you trying to check that PG thinks that a foreign-key relationship
>is installed? In recent versions psql's "\d" will tell you that. If
>you're dealing with an old version you might have to look directly at
>the system catalogs.
>
>Are you not trusting that an active foreign-key relationship has been
>correctly enforced? Then I think you want to do some kind of JOIN
>query to see if you can find any rows with no master row. (You could
>actually do this by temporarily creating a new, redundant FK constraint;
>but if you are feeling that paranoid you're likely not going to trust
>the system's answer anyway...)
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
>
Of course, I use the most simple method of selecting all values which
are not in RI_table, e.g.

# select * from user_table where user_table.value not in (select
RI.value from RI_table);

I had to do this often when I ported from one Postgres-like database
(namely Illustra) into my current Postgres database. I noticed some
rows would not insert into my target table from a text file containing
my source table. So, I created a table like my desired target table but
without referential integrity. Then, on the table w/o RI I did the
above. But, as you can see, I do things as simply as possible.

With great regard for the pros out here in Postgres Land,

Jim Apsey