db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: Obtaining info on UNIQUE constratints.
Date Tue, 02 Oct 2007 13:41:59 GMT
Rick Hillegas wrote:
> 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
> 
> 
Thanks. That helped.

Tim


Mime
View raw message