How to get information on UNIQUE keys on Apache Derby

It’s not easy to get information on derby keys once you created them. I was looking for a command like “SHOW CREATE TABLE ” but no luck. I realized that the answer should lay in SYS tables. After googling a while I found the following bit of wisdom:

The following query will give you the UNIQUE constraints on a table:
select c.constraintname, c.constraintid from sys.systables t, sys.sysconstraints c where t.tablename = ‘FOO’ and t.tableid = c.tableid and c.type = ‘U’;

The following query will return a descriptor object for each constraint on the table. The descriptor will tell you which columns are in each constraint. As noted in the Reference Guide section on SYS.SYSCONGLOMERATES, the descriptor object implements org.apache.derby.catalog.IndexDescriptor. Please note that the descriptor object is not part of Derby' public API and can therefore change from release to release:

select g.descriptor
from sys.systables t, sys.sysconstraints c, sys.syskeys k,
sys.sysconglomerates g
where t.tablename = 'FOO'
and t.tableid = c.tableid
and c.type = 'U'
and c.constraintid = k.constraintid
and k.conglomerateid = g.conglomerateid;