Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: turn on foreign keys

    Hi there,

    I wonder when you turn on the foreign key constraint, is there a way to just get the report which row has the violation, without physically delete and move the row to the exceptional table?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    35
    You will have to export the data to some external csv or delimited file. Then drop the table and recreate it again and try loading the data using load command using the for exception option of the load command and specify the name of the exception table in which the rows violating the constraint will be inserted.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jinsezh
    I wonder when you turn on the foreign key constraint, is there a way to just get the report which row has the violation, without physically delete and move the row to the exceptional table?
    Assuming you're on z/OS, just use the "delete no" option of the "check data" utility.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Aug 2006
    Posts
    33
    Quote Originally Posted by Peter.Vanroose
    Assuming you're on z/OS, just use the "delete no" option of the "check data" utility.
    Here is the syntax for check data:
    .-----------------------------------. |
    | V | .-DELETE--NO----------------. |
    '-FOR--EXCEPTION----IN--table-name1--USE--table-name2-+--+---------------------------+-'
    | .-LOG--YES-. |
    '-DELETE--YES--+----------+-'
    '-LOG--NO--'

    Sorry, I don't know how much you can see it , but anyway, should I still create and specify the exception table "table-name2"?

    Basically I just want to check which rows have the violation and don't want to make any data changes.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jinsezh
    Basically I just want to check which rows have the violation and don't want to make any data changes.
    In that case, just don't use any "FOR EXCEPTION" or "DELETE" options in the statement.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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