db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Obtaining info on UNIQUE constratints.
Date Tue, 25 Sep 2007 13:02:34 GMT
Tim Dudgeon wrote:
> How do you find out which UNIQUE constraints exist for a table, and 
> which columns they use?
> DatabaseMetaData.getIndexInfo( ... ) can give me the information about 
> the indexes, but it seems that in Derby a UNIQUE index is not the same 
> thing as a UNIQUE constraint.
>
> Thanks
>
> Tim
>
Hi Tim,

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
;

Hope this helps,
-Rick


Mime
View raw message