Обсуждение: referential integrity violations
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
"Shaun W. Kruger" <shaun@linuxhost.cc> writes:
> 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.
> ERROR: groupidfk referential integrity violation - key
> referenced from users not found in groups
These are not syntax errors, they are complaints about your data --- ie,
there are rows in the tables that fail to meet the proposed foreign-key
constraint (there is no matching row in the referenced table).
I believe CVS tip has code in it to actually show the offending data
values, but right now you have to find the bad rows the hard way.
A SELECT ... WHERE NOT EXISTS ... kind of query should help.
regards, tom lane
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
On Tue, 10 Jun 2003, 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. > > 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 The problem appears to be that you have invalid data in the tables and so the constraints start off invalidated. From 7.4: NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: groupidfk referential integrity violation - key (groupid)=(0) 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 (justispid)=(0) 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 (uinfoid)=(0) referenced from payments not found in userinfo