Retrieving constraint columns from system tables

[True||False <26...@qq.com>]

Hi,

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,
 
Bennie.

Re: Retrieving constraint columns from system tables

[Rick Hillegas <ri...@oracle.com>]

Hi Bennie,

You may be able to get the information you need by joining the table 
functions which are loaded by the databaseMetaData tool introduced in 
Derby version 10.10.1: 
http://db.apache.org/derby/docs/10.10/tools/rtoolsoptdbmetadata.html

I have included a script which shows how to get some type information 
for index, primary, foreign key, and unique key columns.

Hope this helps,
-Rick

connect 'jdbc:derby:memory:db;create=true';

create table t( a int, b int, c int, primary key ( a, b ) );
create index t_idx on t( c, b );

create table s( x int, y int, z int, foreign key( x, y ) references t( 
a, b ), unique( y, z ) );

-- load the databaseMetaData table functions
call syscs_util.syscs_register_tool( 'databaseMetaData', true );

-- list out the columns and their types for all indexes on T
select
     i.table_schem, i.table_name, i.index_name, i.ordinal_position,
     c.column_name, c.data_type, c.column_size
from
     table( getIndexInfo( null, 'APP', 'T', false, false ) ) i,
     table( getColumns( null, '%', '%', '%' ) ) c
where i.table_schem = c.table_schem
and i.table_name = c.table_name
and i.column_name = c.column_name
order by i.table_schem, i.table_name, i.index_name, i.ordinal_position;

-- list out the columns and their types for the primary key on T
select
     p.table_schem, p.table_name, p.pk_name, p.key_seq,
     c.column_name, c.data_type, c.column_size
from
     table( getPrimaryKeys( null, 'APP', 'T' ) ) p,
     table( getColumns( null, '%', '%', '%' ) ) c
where p.table_schem = c.table_schem
and p.table_name = c.table_name
and p.column_name = c.column_name
order by p.table_schem, p.table_name, p.pk_name, p.key_seq;

-- list out the columns and their types for all the foreign keys on S
select distinct
     k.fktable_schem, k.fktable_name, k.fk_name, k.key_seq,
     c.column_name, c.data_type, c.column_size
from
     table( getImportedKeys( null, 'APP', 'S' ) ) k,
     table( getColumns( null, '%', '%', '%' ) ) c
where k.fktable_schem = c.table_schem
and k.fktable_name = c.table_name
and k.fkcolumn_name = c.column_name
order by k.fktable_schem, k.fktable_name, k.fk_name, k.key_seq;

-- list out the columns and their types for the unique keys on S
select
     i.table_schem, i.table_name, i.index_name, i.ordinal_position,
     c.column_name, c.data_type, c.column_size
from
     table( getIndexInfo( null, 'APP', 'S', false, false ) ) i,
     table( getColumns( null, '%', '%', '%' ) ) c,
     sys.sysconstraints sc
where i.table_schem = c.table_schem
and i.table_name = c.table_name
and i.column_name = c.column_name
and i.index_name = sc.constraintName
and sc.type = 'U'
order by i.table_schem, i.table_name, i.index_name, i.ordinal_position;

-- unload the databaseMetaData table functions
call syscs_util.syscs_register_tool( 'databaseMetaData', false );


On 12/5/13 10:33 PM, True||False wrote:
>
> Hi,
>
> 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.constraintidand  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,
>
> Bennie.
>