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 Fabio Porto <fa...@epfl.ch> on 2006/07/26 19:45:37 UTC

obtaining catalog info on primary and foreign keys

Hello all,

  I'm building an application that accesses Derby catalog to build a dynamic
interface.

I need to obtain primary key and foreign key attributes, does anyone know
where and how those columns

get stored in the catalog? 

 Thanks a lot,

Fabio.

Re: obtaining catalog info on primary and foreign keys

Posted by Daniel John Debrunner <dj...@apache.org>.
fabio.porto@epfl.ch wrote:

> Thank you Jean for your answer.
> 
> In fact, i had found these two tables but the question is how to get
> column names that take part in PKs and FKs..
>  By querying Syskeys and Sysforeignkeys i can identify the keys associated to a
> table but they do not seem to store the information concerning the columns that
> take part on those keys. Something like a relationship between sysconglomerates
> with syscolumns would do it, but unfortunately is not provided.
> 
>  maybe i have missed something, but...

How about the JDBC metadata calls

java.sql.DatabaseMetaData.getImportedKeys
java.sql.DatabaseMetaData.getExportedKeys

Should be standard, rather than having to write code specific to a
database's system tables.

Dan.

Re: obtaining catalog info on primary and foreign keys

Posted by fa...@epfl.ch.
Thank you Jean for your answer.

In fact, i had found these two tables but the question is how to get
column names that take part in PKs and FKs..
 By querying Syskeys and Sysforeignkeys i can identify the keys associated to a
table but they do not seem to store the information concerning the columns that
take part on those keys. Something like a relationship between sysconglomerates
with syscolumns would do it, but unfortunately is not provided.

 maybe i have missed something, but...
 Any idea??
thanks a lot,
Fabio.

> Primary Keys are stored in the SYS.SYSKEYS table.
> Foreign keys are stored in the SYS.SYSFOREIGNKEYS table.
>
> To understand and use the data in those tables, you will need to make a
> join with CONSTRAINTID on the SYS.SYSCONSTRAINTS table CONSTRAINTID.
> To know on which tables the constraints are applied, make a join on the
> SYS.SYSTABLES table with the TABLEID.
>
> I hope this will help you get started,
>
> JF
>
> Fabio Porto wrote:
> >
> > Hello all,
> >
> > I’m building an application that accesses Derby catalog to build a
> > dynamic interface.
> >
> > I need to obtain primary key and foreign key attributes, does anyone
> > know where and how those columns
> >
> > get stored in the catalog?
> >
> > Thanks a lot,
> >
> > Fabio.
> >
>
>

Re: obtaining catalog info on primary and foreign keys

Posted by Jean-Francois Dignard <jf...@broadsoft.com>.
Primary Keys are stored in the SYS.SYSKEYS table.
Foreign keys are stored in the SYS.SYSFOREIGNKEYS table.

To understand and use the data in those tables, you will need to make a 
join with CONSTRAINTID on the SYS.SYSCONSTRAINTS table CONSTRAINTID.
To know on which tables the constraints are applied, make a join on the 
SYS.SYSTABLES table with the TABLEID.

I hope this will help you get started,

JF

Fabio Porto wrote:
>
> Hello all,
>
> I’m building an application that accesses Derby catalog to build a 
> dynamic interface.
>
> I need to obtain primary key and foreign key attributes, does anyone 
> know where and how those columns
>
> get stored in the catalog?
>
> Thanks a lot,
>
> Fabio.
>