Re: [GENERAL] Verifying Referential Integrity

[Jimmie H. Apsey]

Tom Lane wrote: "Geisler, Jim" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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