Would be great to capture Jeff's info on System catalogs in a new entry off http://db.apache.org/derby/papers/index.html#Derby+Engine
Then one can add onto it for additional details or catalogs that are missing...having a dedicated entry about derby's system catalog (metadata) would be neat indeed...Just some thoughts... On 1/30/06, Jeffrey Lichtman <[EMAIL PROTECTED]> wrote: > > >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/ > >
