Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Question Unanswered: dropping check constraint

    Hi everybody,

    I am very new to DB2 and would like to ask about the impact of dropping and recreating a check constraint.

    I have a table column which only allows certain values enforced by the check constraint. I want to add another value to that constraint. According to the alter table check constraint syntax on IBM website I cannot find a way on altering the constraint without dropping and recreating it with the additional check value.

    I have noticed that rebinds will be necessary since certain packages will become invalid. Also there is a Foreign Key and Index on that column. Will this have any effect on the integrity of the database? What is the impact on dropping and creating the constraint? Will the table/database become in some sort of check pending state?

    Thank you very much for your time and help
    Regards

    Para
    DB2 9.7 on AIX 6.1

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by paramedic View Post
    What is the impact on dropping and creating the constraint? Will the table/database become in some sort of check pending state?
    The behaviour is clearly explained in the manual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest you try it in a completely test environment and observe the behaviour.

    If somethbing happens that you do not understand, post what you tried and what happened. Someone should be able to clarify.

  4. #4
    Join Date
    Sep 2012
    Posts
    5
    the original table checked the column TESTCOLUMN for values A,B,C
    ALTER TABLE TEST.TESTTABLE add constraint TESTCHKC
    check ( TESTCOLUMN ('A', 'B', 'C') ) ;
    now I wanted it to additionally check for 'D', so what I did was
    altering the existing table to drop the check constraint

    db2 alter table TEST.TESTTABLE drop check TESTCHKC;
    meanwhile the column would allow all kinds of values. So I hurried with adding the constraint again:

    ALTER TABLE TEST.TESTTABLE add constraint TESTCHKC
    check ( TESTCOLUMN ('A', 'B', 'C', 'D') ) ;
    I have checked the database for anything in pending status but luckily didnt find anything. The only thing which had to be done was rebinding invalid packages. Any Foreign keys or indexes on that testcolumn seem not to be affected, also the parent tables. But what if during the drop and create some different values like E,G etc... were inserted? According to the manuel the creation of check constraint will fail, how can I prevent or handle this issue?

    edit:

    Ok for those interested in what I found regarding the above mentioned problem, the following steps can prevent insertion into the table so that creation of the check constraint will fail:

    -create exception tables
    -set integrity off for that table to bring it into check pending state
    -drop the check constraint
    -create check constraint
    -rebind the invalid packages
    -run set integrity to bring tables out of check pending state
    Last edited by paramedic; 09-24-12 at 06:43.

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Thanks for posting what you learned

Tags for this Thread

Posting Permissions

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