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
