Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Red face Unanswered: Disabling Foreign Key

    Is it possible to disable a foreign in DB2 v8 Universal Edition.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to "Drop" it.

    Andy

  3. #3
    Join Date
    Sep 2003
    Posts
    85
    Depending on what is desired/required, you may want to look into having the RI but specifying NOT ENFORCED (for the constraint-attributes).
    Ruby

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Ruby,

    What do you mean wtth RI and NOT ENFORCED ?

    Quote Originally Posted by rubystep
    Depending on what is desired/required, you may want to look into having the RI but specifying NOT ENFORCED (for the constraint-attributes).
    Ruby

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    Since you're talking about a FK (Foreign Key), then we're dealing with RI (Referential Integrity).
    So, what NOT ENFORCED (at least this feature is available in UDB ESE aka version 8) says is that I will define the RI with the tables but don't enforce the RI rules. Note that since the default for a table is ENABLE QUERY OPTIMIZATION, the Optimizer will take into consideration the fact of RI definitional existence without RI rules enforcement with respect to how to access data.
    HTH,
    Ruby

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Ruby,

    Do I interprete this correctly :
    Code:
    ALTER TABLE table1
    ADD CONSTRAINt fkxx FOREIGN KEY (col1)
    REFERENCES table2 (colx) NOT ENFORCED;
    Means: define a pro forma definition of a FK (RI), but don't check if this is true at the moment of the definition or after an INSERT, UPDATE, DELETE, IMPORT...

    When would I want to define a RI but not enforce it ? Performance reasons probably. But then why define a RI if it's not guaranteed ? For documentation purposes ?

    I have worked for a short time at a company where I had to clean up their database where the tables had no PK and there were no FK's defined. Multiple (nearly) identical records, references to records that didn't exist or multiple times, the legacy program (source code lost) that would continue to pour in new garbage in the database as I cleaned it, ... it drove me mad. It made me very sensitive to things like not enforcing a FK.

    Can this be used in certain circumstances when it would be handy if the RI would not be enforced during each and every step of a transaction, but only at the end of the transaction ?

    Code:
    ALTER TABLE table1
    ALTER FOREIGN KEY fkxx NOT ENFORCED;
    
    ... multiple SQL commands in a sequence that may at some times 
    violate FK constraints
    ...
    
    ALTER TABLE table1
    ALTER FOREIGN KEY fkxx ENFORCED;
    But wouldn't that initiate a total table (as opposed to just the record(s) affected) check to verify the RI ?

    So, what NOT ENFORCED (at least this feature is available in UDB ESE aka version 8) says is that I will define the RI with the tables but don't enforce the RI rules. Note that since the default for a table is ENABLE QUERY OPTIMIZATION, the Optimizer will take into consideration the fact of RI definitional existence without RI rules enforcement with respect to how to access data.
    Wim

  7. #7
    Join Date
    Sep 2003
    Posts
    85
    Assume that the "insertion" of new data is done in the correct RI sequence (insertion could be INSERT, LOAD), then one woldn't want the performance overhead of UDB checking for RI.
    Also, the NOT ENFORCED would be there for documentation purposes and there are times when the Optimizer will use this information to best access the data.
    HTH,
    Ruby

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Assume that the "insertion" of new data is done in the correct RI sequence (insertion could be INSERT, LOAD), then one woldn't want the performance overhead of UDB checking for RI.
    That's possibly what those programmers of that venerable legacy program must have thought. (no pun intended) My stomach still feels like a knot when I think about it.

    I can understand that in special cases it can be necessary to avoid the performance overhead in a production environment. But in that case, the FK constraints should be enforced in the development and test databases, so the production database can be pretty sure to be on the safe side.

    Wim

Posting Permissions

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