Mamta Satoor wrote:
Hi,
I have researched quite a bit on Derby-655 : getImportedKeys returning
duplicate rows in some cases. Here are my findings and 3 possible
solutions to the problem. After reading the mail, if someone can think
of any other way of solving this problem, then please share it.
==== SNIP ==
In the above sql snippet, when the join is made on SYSCONSTRAINTS,
SYSKEYS, and SYSCONGLOMERATES for primary key on table t2, the query
finds 2 rows in SYSCONGLOMERATES with the conglomerateid and it picks
both those rows. This is wrong and we need to somehow return only one
row from SYSCONGLOMERATES, eventhough there might be duplicate rows in
there for a given conglomerateid.
I have thought of following 3 solutions
1)Even if a foreign key's backing index qualifies as a duplicate
index, have foreign key backing index create its own conglomerate
rather than share an existing conglomerate. But this would mean that
system would have to maintain 2 identical conglomerates. This can
impact Derby's performance negatively, although I don't know how badly
because it might be rare that a user creates a primary key and foreign
key on identical columns.
2)Write a system function which will return just one row for all the
duplicate conglomerateids for a given constraintid and use that row in
the outermost select statement of metadata query for getImportedKeys.
Not sure how easy this would be to implement but I am leaning towards
this solution.
3)Do not create duplicate rows in sysconglomerates for duplicate
indexes. Instead have just one row in sysconglomerate to represent all
the duplicate indexes in sysconstraints. But that seems like more of
an enhancement rather than a bug fix to me. Current Derby code heavily
relies on having a row in SYSCONGLOMERATE for each of the indexes.
Thanks if you have made it this far :) I hope this mail is clear in
explaining the problem and the possible solutions. If anything is not
clear, then let me know and I can provide more information.
If anyone has any ideas about some other possible fix for the problem,
please let me know that too.
thanks,
Mamta
The table needs a primary key - besides making conglomerateid unique it
might be less invasive to add a system generated primary key to this
table. I personally avoid using tables without primary keys because you
can run into odd-ball situations like this where multiple records can be
identical and so it is not possible to get just one record.
With a primary key you can get a single record by adding a correlated
subquery to the query conditions that specify using the record with the
record with max or min value of the unique column. Say the unique key
column is conglomeratePK - the conditional would be:
select * from sys.sysconglomerates c1 where c1.conglomeratePK = (
select max(c2.conglomeratePK ) from sys.sysconglomerates c2 where
c2.conglomerateid = c1.conglomerateid);
