4

I have some csv formatted table dumps and loaded those into a new database with the tables defined by a DDL script from the source database (both acquired by somebody else so I cannot have a look at the original production database). Before loading the rows, I disabled all foreign key constraints (via ALTER TABLE xyz ALTER FOREIGN KEY fkname NOT ENFORCED) to avoid having to care for the load order. Now I want to reenable those constraints with the following statement

ALTER TABLE xyz ALTER FOREIGN KEY fkname ENFORCED

and some of them fail. The error message generated is

SQL0677N: The FOREIGN KEY "fkname" cannot be created because the table 
contains rows with foreign key values that cannot be found in the parent key
of the parent table. SQLSTATE=23520

That reads straightforward and clear but I cannot locate the erroneous rows.

An example: T02 is the name of a parent table and T24 the name of a child table. The primary key is (T02.A, T02.B, T02.C) and the corresponding foreign key columns are (T24.A0, T24.B0, T24.C0), the constraint is called F02 and all the named columns are of type CHAR with lengths 2, 4 and 7, respectively.

I tried finding the breaking rows with the following statement:

(SELECT T24.A0, T24.B0, T24.C0 FROM T24)
EXCEPT
(SELECT T24.A0, T24.B0, T24.C0 FROM T24, T02
WHERE T02.A = T24.A0 AND T02.B = T24.B0 AND T02.C = T24.C0)

but it returns an empty result set.

Are there any other circumstances which may cause this error and how do I investigate them?

The database server in question is DB2 10.1.0 for Linux/Unix/Windows (running under Windows Server 2008 R2 64-Bit).

  • Try disabling query optimization on the RI constraint -- the default is to trust the constraint and use it for query optimization, even if it's not enforced, so the query may not actually do what you think it does. – mustaccio Apr 16 '14 at 15:48
  • @mustaccio that did it, I found the offending rows. Thank you! I would upvote your comment if I had the necessary reputation to do so... Would you like to post your suggestion as an answer so I can accept it? – JayK Apr 24 '14 at 9:24
  • SELECT A0, B0, C0 FROM T24 EXCEPT SELECT A0, B0, C0 FROM T02 ; would be a simpler query (no idea if it would work any different though ;) – ypercubeᵀᴹ Apr 14 '16 at 8:35
2

The DB2 optimizer relies on foreign key constraints, which by default are enabled for optimization even though they may not be enforced. The optimizer may choose not to read the rows that should not exist according to the constraint.

Try disabling query optimization on the RI constraint:

ALTER TABLE xyz ALTER FOREIGN KEY fkname DISABLE QUERY OPTIMIZATION

1

I had the same problem. I set all foreign keys not enforced, then I loaded the tables and set the foreign keys enforced. Several fail. I do a query:

SELECT TABSCHEMA, TABNAME, STATUS
FROM SYSCAT.TABLES 
where status = 'C'

For the tables, that are shown, I do a:

set integrity for table immediate checked

This works. Afterwards I can set all foreign keys enforced.

0

You could use the SET INTEGRITY statement to locate the offending rows. You can have the offending rows placed in a table T24_EXCEPTIONS that is a copy DDL-wise of T24. You can also check the parent T02 at the same time and use a similar tactic.

SET INTEGRITY FOR T02,T24 IMMEDIATE CHECKED
FOR EXCEPTION IN T02 USE T02_EXCEPTIONS,
IN T42 USE T24_EXCEPTIONS

See DB2 10.1 Information Center for details.

  • This is among the things which I tried (with a single table, T24 in this case) but SET INTEGRITY succeeded without revealing any constraint violations (the exception table stayed empty). Maybe it does not work while the constraint is not enforced? Nevertheless, I will try it with both tables at the same time like you suggested, to see whether it makes a difference, when I am on-site again next week. – JayK Apr 16 '14 at 16:18
  • I would assume you need the constraint enforced. – Chris Aldrich Apr 16 '14 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.