db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "True||False" <261042...@qq.com>
Subject Retrieving constraint columns from system tables
Date Fri, 06 Dec 2013 06:33:14 GMT

I'm trying to retrieve the columns for constraints (PK, FK, unique, check) from system tables
and looking through the documentation I cannot see how I can get that information. SYS.SYSCONSTRAINTS
does not contain column information. I normally would expect something like SYS.CONSTRAINTCOLUMNS.
For example,I want to know which column belong to for the primary key. I find the tables in
sys schema then I can get all the primary key info. The SQL is:
select t.tablename, conglomeratename backIdxName, cst.constraintname, cst.type from sys.systables
  t, sys.sysconstraints   cst, sys.sysconglomerates cgl, sys.syskeys          sk where isindex
= 'TRUE' and cgl.tableid = t.tableid and (sk.constraintid = cst.constraintid and cst.type
= 'P' and sk.conglomerateid = cgl.conglomerateid) and t.tableid = cst.tableid and t.tabletype
= 'T' 
All the primary key is query out, but I wanna know which column belong to for the primary
key. There's no column info in sys.sysconstraints..

I must be missing something obvious here. The same is true for indexes. I cannot find any
information regarding them either in the system tables. Is there any other way to retrieve
the information? java.sql.DatabaseMetadata only does part of the job.

Thanks in advance,
View raw message