ListKeyIndexes

Listing Primary and Foreign Key Index names

The following query will return a list of the Primary and Foreign keys for each user table in a Derby database along with the name of the backing index. [Usage note: to cut and paste this query into the Derby IJ tool you need to increase the IJ display width setting - the following command did the trick for me: maximumdisplaywidth 175; ]

select t.tablename, conglomeratename backIdxName, 
     cst.constraintname, cst.type 
from sys.systables t, sys.sysconstraints cst,
     sys.sysconglomerates cgl, sys.syskeys sk
where isindex = 'TRUE' 
     and cgl.tableid = t.tableid
     and (sk.constraintid = cst.constraintid 
          and cst.type='P'and sk.conglomerateid = cgl.conglomerateid) 
     and t.tableid = cst.tableid 
     and t.tabletype = 'T'
UNION
select t.tablename, conglomeratename backIdxName, 
     cst.constraintname, cst.type 
from sys.systables t, sys.sysconstraints cst,
     sys.sysconglomerates cgl, sys.sysforeignkeys fk
where isindex = 'TRUE' 
     and cgl.tableid = t.tableid
     and (fk.constraintid = cst.constraintid 
          and cst.type='F' and fk.conglomerateid = cgl.conglomerateid)
     and t.tableid = cst.tableid 
     and t.tabletype = 'T'
order by tablename, type