db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject When foreign key is dropped, is Derby dropping the wrong row from SYS.SYSCONGLOMERATES?
Date Sat, 20 May 2006 08:40:43 GMT
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

Mime
View raw message