The tables referenced by this query in particular are SYS.SYSTABLES,
SYS.SYSSCHEMAS, SYS.SYSCONSTRAINTS, SYS.SYSFOREIGNKEYS,
SYS.SYSCONGLOMERATES, SYS.SYSCOLUMNS, SYS.SYSKEYS.
I'm not sure what you need, but I'll try to give an idea of the
relationships between these tables.
SYSTABLES has one row for each table in the database. Its primary key
is TABLEID, which contains system-generated values. The SCHEMAID is a
foreign key column, which references SYSSCHEMAS.
SYSSCHEMAS has one row for each schema in the database. Its primary
key is SCHEMAID.
SYSCOLUMNS has one row for each column of every table in the
database. Its primary key is (REFERENCEID, COLUMNNUMBER). REFERENCEID
is also a foreign key, which refers to TABLEID in SYSTABLES.
SYSCONSTRAINTS has one row for each constraint in the database
(primary, unique, foreign and check constraints). Its primary key is
CONSTRAINTID, which is a system-generated value. The TABLEID column
is a foreign key referring to SYSTABLES.TABLEID. The SCHEMAID column
is a foreign key referring to SYSSCHEMAS.SCHEMAID.
SYSCONGLOMERATES has one row for each heap (base table) and index in
the database. Its primary key is CONGLOMERATEID, which is a
system-generated value. The TABLEID column is a foreign key referring
to SYSTABLES.TABLEID. The SCHEMAID column is a foreign key referring
to SYSSCHEMAS.SCHEMAID. The ISCONSTRAINT column tells whether the row
represents the backing index for a primary key, foreign key or unique
constraint.
SYSKEYS has one row for each primary key or unique constraint in the
database. Its primary key is CONSTRAINTID, which is also a foreign
key to SYSCONSTRAINTS.CONSTRAINTID. The CONGLOMERATEID column is a
foreign key referring to SYSCONGLOMERATES.CONGLOMERATEID.
SYSFOREIGNKEYS has one row for each foreign key in the database. Its
primary key is (CONSTRAINTID, KEYCONSTRAINTID). The CONSTRAINTID
column is a foreign key refererring to SYSCONSTRAINTS.CONSTRAINTID,
and it contains the constraint id of the foreign key itself. The
KEYCONSTRAINTID is a foreign key referring to
SYSCONSTRAINTS.CONSTRAINTID, and it contains the constraint id of the
primary key that the foreign key refers to. The CONGLOMERATEID column
is a foreign key that refers to SYSCONGLOMERATES.CONGLOMERATEID, and
it contains the id of the backing index for the foreign key.
By the way, last night I did a manual run-through of the query with
Mamta's example, and I didn't find anything wrong. I would suggest to
anyone working on this problem to work from the outside in - figure
out the results of the two table subqueries (FKTB and FKINFO) before
looking at the outer query.
It's possible that the bug isn't in the query itself. That is, the
bug could be in query compilation or execution. It might be a good
idea to compare the query plans and runtime statistics for Derby and
Cloudscape 5.1.60 (since Kathey says it works in Cloudscape).
- Jeff Lichtman
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
