When foreign key is dropped, is Derby dropping the wrong row from SYS.SYSCONGLOMERATES?

[Mamta Satoor]

Hi,

 

I wrote a simple test case involving 2 tables with primary keys and foreign key constraint on one of them. Later, when I drop the foreign key, it appears that Derby is dropping incorrect row from SYSCONGLOMERATES.

 

Here is the ij session demonstrating the problem

ij> CREATE TABLE t3(c31_ID BIGINT NOT NULL PRIMARY KEY);
0 rows inserted/updated/deleted
ij> CREATE TABLE t2 (c21_ID BIGINT NOT NULL REFERENCES t3(c31_ID) ON DELETE CASCADE ON UPDATE NO ACTION, primary key (c21_id));
0 rows inserted/updated/deleted

ij> select tableid from sys.systables where tablename ='T2';
TABLEID
------------------------------------
8ca44062-010b-50e3-8d63-000000156130

1 row selected

--*********************************************2 ROWS IN SYSCONSTRAINTS FRO T2, 1 FOR PRIMARY KEY AND OTHER FOR FOREIGN KEY*********************************************

ij> select constraintid, constraintname, type, state, referencecount from sys.sysconstraints where tableid = 8ca44062-010b-50e3-8d63-000000156130;
CONSTRAINTID                        |CONSTRAINTNAME     |&|&|REFERENCEC&
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

dcd58064-010b-50e3-8d63-000000156130|SQL060520012247770     |P|E|0      <----------- PRIMARY KEY FOR T2
e5214067-010b-50e3-8d63-000000156130|F_443                             |F|E|0      <----------- FOREIGN KEY FOR T2

2 rows selected

--*********************************************3 ROWS IN SYSCONGLOMERATES FOR TABLE T2, 1 FOR THE HEAP, 1 FOR PRIMARY KEY AND 1 FOR FOREIGN KEY*********************************************

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

--*********************************************DROP THE FOREIGN KEY ON T2 AND SEE WHAT HAPPENS TO SYSTEM TABLES*********************************************

ij> alter table t2 drop constraint F_443;
0 rows inserted/updated/deleted

--*********************************************SYSCONGLOMERATE ENTRY FOR PRIMARY KEY GOT DROPPED RATHER FOR FOREIGN KEY*********************************************

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|SQL060520012250890                                |817                 |true |UNIQUE BTREE (&|true    <---------FOREIGN KEY FOR T2----This should have been dropped?????

2 rows selected

--*********************************************SYSCONSTRAINTS STILL HAS PRIMARY KEY FOR T2 WHICH IS RIGHT*********************************************

ij> select constraintid, constraintname, type, state, referencecount from sys.sysconstraints where tableid = '8ca44062-010b-50e3-8d63-000000156130';
CONSTRAINTID                        |CONSTRAINTNAME     |&|&|REFERENCEC&
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

dcd58064-010b-50e3-8d63-000000156130|SQL060520012247770     |P|E|0  <--------------------PRIMAY KEY FOR T2---correct

1 row selected

 

As can be seen from the above example, although user dropped foreign key F_443 from table T2, Derby dropped conglomerate entry for primary key from SYSCONGLOMERATES. Is my interpretation correct and is this a bug in Derby?

Mamta