db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: [Derby-655] : getImportedKeys returns duplicate rows in some cases
Date Wed, 24 May 2006 20:48:48 GMT
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);

View raw message