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: How can I fetch constraint attribute on Column Level from SYS Tables??
Date Fri, 19 Sep 2008 15:24:49 GMT
Hi Takashi,

If you are just looking for primary keys, you may find the information 
you need in DatabaseMetaData.getPrimaryKeys(). If you are just looking 
for a table's foreign keys, you may find the information you need in 

You may also find more information in the DESCRIPTOR column of 
SYS.SYSCONGLOMERATES. The following query will give you this object:

select tab.tablename, con.constraintname, cong.descriptor
from sys.sysconstraints con, sys.systables tab, sys.syskeys keys, 
sys.sysconglomerates cong
where con.constraintid = keys.constraintid
and keys.conglomerateid = cong.conglomerateid
and tab.tableid = con.tableid
and tab.tablename = ?

The DESCRIPTOR column holds a fairly informative object of type 
org.apache.derby.catalog.IndexDescriptor. Please note that 
IndexDescriptor is not part of Derby's public api and so you are not 
guaranteed that these objects will behave compatibly in future releases. 
On the bright side, I am not aware of any incompatible changes to this 
interface over the last 4 years.

Hope this helps,

Takashi Saruhashi wrote:
> Hello all,
> My name is Takashi. Now, I want to fetch constraint attributes on Column
> level from SYS Tables.
> Does anyone know how to fetch it??
> I checked Derby's reference manual here,
> http://db.apache.org/derby/docs/10.3/ref/rrefsistabs38369.html
> Actually, in SYSCOLUMNS Table, there's no association with
> SYSCONSTRAINTS Table and vice versa...
> However, when I create Table, I can assign some constraints on Column
> level indeed.
> Therefore, I guess there should be some way to fetch it on Column
> level...
> Any suggestion is quite appreciated.
> Best regards.
> Takashi

View raw message