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
ij> select conglomerateid, conglomeratename, conglomeratenumber, isindex, descriptor, isconstraint from sys.sysconglomerates where tableid = 8ca44062-010b-50e3-8d63-000000156130';
CONGLOMERATEID |CONGLOMERATENAME |CONGLOMERATENUMBER |ISIN&|DESCRIPTOR |ISCO&
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
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 <[EMAIL PROTECTED]> 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.
