db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Retrieving constraint columns from system tables
Date Fri, 06 Dec 2013 15:55:13 GMT
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.
>


Mime
View raw message