So if I try to insert tuple 1 first, I get a ref. constraitn violation. I am coming from the Informix world, where a simple
SET CONSTRAINTS ALL DEFERRED
would allow me to insert all the tuples without constraint checking, and enabling checking afterwards, when all references are valid.
Unfortunately I haven't figurred out how to do such a thing in DB2 (Version 8.1). I believe the key is the SET INTEGRITY statement. I played around with that a little, but found it most confusing. It seems I was able to disable constraitn checking, but then the table was in "check pending" state and wouldn't allow any inserts.
So is there a way to insert many circularly linked tuples by TEMPORARILY disabling the ref. constraint checking?
Well, as I wrote in my first post, I did.
IMO the DB2 documentation is a plain disaster. The best success was that I got the table to be in check-pending-state, which wouldn't allow inserts. I was hoping for a hint like
"try 'SET INTEGRITY FOO BAR MAGIC HERE'".
Perhaps I am just a bit spolied being used to the much better Informix manuals.
Sorry, I missed your reference to SET INTEGRITY in your first post. When you use SET INTEGRITY to put a table in check-pending state, all you can do to it is LOAD data. If this is not an option, I might suggest dropping the foreign key constraint, then perform your inserts, the redefine the foreign key. (dumb, I know, but it should work).
The given example was overly simplistic. It is actually more difficult. FYI I am trying to store geometric data in the DB. I am trying to represent solids using the classical winged-edge datastructure. So for every edge of my solid I have 4 references to other edges.
BTW: I tried switching constraints for that table to off, but I ended in check pending status for that table. INSERT won't work, and I am not yet familiar with DB2's "load". I try to avoid any dbms-specific extensions to the best I can, db2's load seems rather proprietary.