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.
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
