db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: [Derby-655] : getImportedKeys returns duplicate rows in some cases
Date Thu, 25 May 2006 16:53:27 GMT
Hi Dan,

Thanks for your alternative suggestions. Seems like option 1 of the 2 ways
suggested by you might be faster than the 2nd one.

As for DERBY-1343, I don't understand how a check for column isconstraint to
be true can fix the problem. I am copying following sql from
http://www.nabble.com/When+foreign+key+is+dropped,+is+Derby+dropping+the+wrong+row+from+SYS.SYSCONGLOMERATES--t1654121.html#a4481463
 ij> select conglomerateid, conglomeratename, conglomeratenumber, isindex,
descriptor, isconstraint from sys.sysconglomerates where tableid =
8ca44062-010b-50e3-8d63-000000156130';
CONGLOMERATEID                      |CONGLOMERATENAME
|CONGLOMERATENUMBER  |ISIN&|DESCRIPTOR     |ISCO&
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a4bcc063-010b-50e3-8d63-000000156130|8ca44062-010b-50e3-8d63-000000156130
|800                 |false|NULL
|false <--------------------HEAP FOR T2
848c0061-010b-50e3-8d63-000000156130|SQL060520012247770
                |817
|true |UNIQUE BTREE (&|true  <--------------------PRIMARY KEY FOR T2
848c0061-010b-50e3-8d63-000000156130|SQL060520012250890
|817                 |true |UNIQUE BTREE (&|true  <--------------------
FOREIGN KEY FOR T2
3 rows selected
Both the duplicate rows in SYSCONGLOMERATES table for table T2, have their
isconstraint set to true, so there is still not a way to uniquely identify
foreign key conglomerate row from primary key conglomerate row while
dropping the foreign key constraint.

thanks,
Mamta
On 5/25/06, Daniel John Debrunner <djd@apache.org> wrote:
>
> Mamta Satoor wrote:
>
> > But for the existing duplicate foreign keys in the database, there will
> > still be duplicate conglomerateids in SYSCONGLOMERATES and that will
> make
> > getImportedKeys return incorrect number of rows. We can solve this in
> > upcoming Derby10.2 release by adding a system generated primary key to
> > SYSCONGLOMERATES (as suggested by Stan). This will make sure that
> existing
> > duplicate rows with same conglomerateid can be uniquely identified using
> > system generated primary key. getImportedKeys will need to be fixed as
> > shwon
> > by Stan's example query. This can only be done for Derby10.2. But for
> older
> > Derby releases, I don't think we can add a new column to a system table
> for
> > compatibility reasons and hence older releases will continue to return
> > incorrect number of rows from getImportedKeys for existing duplicate
> > foreign
> > key indexes.
> >
> > Someone should correct if I am missing something here.
>
> I don't think we should be creating a new column in the table for this.
> Seems like it would be possible to fix up the data in the table so that
> the data is unique in the conglomerateid column. I can think of two ways
> to do this:
>
> 1) Have upgrade code that patches the system tables
>
> 2) Have upgrade code that drops & re-creates indexes/constraints that
> have this problem.
>
> Seems like the issue with dropping the wrong row on a drop constraint
> (DERBY-1343) could be fixed with additional qualifications on the row
> search, ie. require the isconstraint column to be true.
>
> Dan.
>
>
>

Mime
View raw message