You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Tim Dudgeon <td...@informaticsmatters.com> on 2007/09/24 21:56:10 UTC
Obtaining info on UNIQUE constratints.
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
Re: Obtaining info on UNIQUE constratints.
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
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
Re: Obtaining info on UNIQUE constratints.
Posted by Rick Hillegas <Ri...@Sun.COM>.
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