Mamta Satoor wrote: > Hi, > > I have been looking at Derby-655 getImportedKeys returns duplicate rows > in some cases. Deepa reported that one of the databases with just toooo > many tables was returning duplicate rows for > DatabaseMetaData.getImportedKeys on a particular table. I was able to > work on that database and bring it down to 3 tables which are involved > in the getImportedKeys call. Following is the sql which will show the > relationship between the 3 tables. > > CREATE TABLE t1(c11_ID BIGINT NOT NULL); > CREATE TABLE t2 (c21_ID BIGINT NOT NULL primary key); > ALTER TABLE t1 ADD CONSTRAINT F_12 Foreign Key (c11_ID) > REFERENCES t2 (c21_ID) ON DELETE CASCADE ON UPDATE NO ACTION; > CREATE TABLE t3(c31_ID BIGINT NOT NULL primary key); > ALTER TABLE t2 ADD CONSTRAINT F_443 Foreign Key (c21_ID) > REFERENCES t3(c31_ID) ON DELETE CASCADE ON UPDATE NO ACTION; > > t1(c11_id) has foreign key reference to t2(c21_id) which in turn has > foreign key reference to t3(c31_id). Now if a jdbc program tries to > invoke DatabaseMetaData.getImportedKeys on t1, it returns 2 rows, one > for each chained foreign key reference.
Is there anything significant when you say "it returns 2 rows, one > for each chained foreign key reference"? Just that it returns the same row twice, so I'm wondering why you say "each chained reference". <snip - big ugly query> My only advice is to break the query down from its inner elements out. Ensure each of those in isolation is returning the correct data. Then work on the next level out. Maybe even creating a view for the working inner elements so the next one to tackle is somewhat readable. E.g. with something like SELECT * FROM T, (SELECT * FROM A,B WHERE ...) AS X WHERE ... Start with SELECT * FROM A,B WHERE ... ensure that works, then do create view SUB_AB AS SELECT * FROM A,B WHERE ... then work on SELECT * FROM T, SUB_AB WHERE ... Hope this is clear, just an idea to make the SQL visually understandable. Maybe remove all the optimizer overrides as well to clear out the clutter. Dan.
