Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Question Unanswered: How to disable referential constraint checking during bulk insert?

    I have the following problem:

    I am trying to insert several new tuples into a table which has attrbiutes referencing itself. The tuples I insert are referencing themselfes circularly, e.g.:

    Tuple 1 references Tuple 2
    Tuple 2 references Tuple 3
    Tuple 3 references Tuple 1

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at the SET INTEGRITY command in the SQL manual.

    Andy

  3. #3
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Unhappy

    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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).

    HTH

    Andy

  5. #5
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Angry

    Thanks for the help. Unfortunately, I had hoped for a different answer.
    I REALLY begin to dislike DB2.

    I want my Informix Dynamic Server back!

  6. #6
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Wink

    Try the following approach:

    1.
    Tuple 1 references Tuple 2
    Tuple 2 is still not loaded, so set the constraint check for Tuple 1 off
    SET INTEGRITY FOR <your_table_tuple_1> OFF
    LOAD Tuple 1

    2.
    Now you have the data you need for loading Tuple 3 (references Tuple 1)
    LOAD Tuple 3

    3.
    Now you have the data you need for loading Tuple 2 (references Tuple 3)
    LOAD Tuple 2

    4.
    Now you must check if the data you loaded at 1 is correct (no referential constraint violation)
    SET INTEGRITY FOR <your_table_tuple_1> IMMEDIATE CHECKED FOR EXCEPTION... and so on.

    HTH.
    Rodney Krick

  7. #7
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14
    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.

  8. #8
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    1. Drop the foreign key constraints
    2. Load /Import the data
    3 Recreate the constraints

    You Can only drop the constraints. There is no concept like turning them off

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •