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).
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 ;)