How can I fetch constraint attribute on Column Level from SYS Tables??

[Takashi Saruhashi <TS...@valueprice.eu>]

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

Re: How can I fetch constraint attribute on Column Level from SYS Tables??

[Rick Hillegas <Ri...@Sun.COM>]

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 
DatabaseMetaData.getImportedKeys().

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,
-Rick


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
>