Re: referential integrity violations - Mailing list pgsql-general

Shaun W. Kruger wrote:

> I'm getting some pretty strange results when doing a mass create of my
> database structure.  I get the following when it goes to create the
> foreign keys.  I just can't figure out why it is that half of them
> complain and the other half don't.

Because half of them are satisfied, and the other half are not :-)

For example, user root (id 14) has groupid 0, that does not exist in the
groups table...

This is, of course, just the first violation that jumped at me. There
are many more of them actuially...

I hope, it helps...
Dima

>
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER TABLE
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  groupidfk referential integrity violation - key referenced
> from users not found in groups
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER TABLE
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  justispidfk referential integrity violation - key referenced
> from userinfo not found in ispusers
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER TABLE
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  uinfoidfk referential integrity violation - key referenced
> from payments not found in userinfo
>
> ---- Here are the first 3 foreign key definitions.  The middle one
> fails while it has the same syntax of the third one.
> ---- They both have a field called groupid and both are declared the
> same way in users and userinfo.
> ALTER TABLE ONLY groups
>     ADD CONSTRAINT orgidfk FOREIGN KEY (orgid) REFERENCES
> organization(id) ON UPDATE CASCADE ON DELETE SET NULL;
>
> ALTER TABLE ONLY users
>     ADD CONSTRAINT groupidfk FOREIGN KEY (groupid) REFERENCES
> groups(id) ON UPDATE CASCADE ON DELETE SET NULL;
>
> ALTER TABLE ONLY userinfo
>     ADD CONSTRAINT groupidfk FOREIGN KEY (groupid) REFERENCES
> groups(id) ON UPDATE CASCADE ON DELETE SET NULL;
>
> The exact database definition is at
> http://linuxhost.cc/~shaun/brokendb.txt  I have been using
> $ cat brokendb.txt |psql -U <uname> <dbase name> &> output.errors
>
> If anyone has run into this kind of problem suggestions would be very
> welcome.  This just doesn't seem to add up. I've been using
> "PostgreSQL Introduction and Concepts" by Bruce Momjian as my guide so
> far, but when I have seemingly identicle foreign key definitions and
> one is failing there just isn't anything in the book that covers
> that.  One other thing of note, I only gave the first 3.  There are 6
> foreign keys and 3 of them are failing (every other one).
>
> Shaun Kruger
> shaun@linuxhost.cc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org