cwkuok wrote:

> but now i want to know part of tbl2_tbl1_id (fk) --> tbl1_id (pk). Is
> there any system fields indicate these relationships ?? perhaps
> having some indicators e.g. id that show tbl2_tbl1_id (fk) --->
> tbl1_id (pk) ??

If all you need is just to find for a certain table from which tables
does it get its foreign keys (=referenced tables), then you should just
look at RDB$RELATION_CONSTRAINTS and RDB$REF_CONSTRAINTS. In the latter
table you practically have tuples of constraints named
RDB$CONSTRAINT_NAME and RDB$CONST_NAME_UQ. Now in your example you'd
want to find out for the table "table2" which table(s) does it reference:
1) first you find out the RDB$CONSTRAINT_NAME by something like:
select RDB$CONSTRAINT_NAME from RDB$RELATION_CONSTRAINTS
where RDB$CONSTRAINT_TYPE='FOREIGN KEY'
and RDB$RELATION_NAME='table2';
2) then you find out the RDB$CONSTRAINT_NAME values for referenced
tables by looking at the RDB$REF_CONSTRAINTS table, something like:
select RDB$CONST_NAME_UQ from RDB$REF_CONSTRAINTS
where RDB$CONSTRAINT_NAME='<the value from the previous step>';
3) lastly you just list the table names contained within those
constraints you got in the previous step:
select RDB$RELATION_NAME from RDB$RELATION_CONSTRAINTS
where RDB$CONSTRAINT_NAME='<one of the values from the previous step>';

Of course you could also find out all foreign keys (fields) plus foreign
table names and corresponding field names, but explaining this would
take considerable amount of time on my part; I recommend you study the
IB6 documentation freely available at www.ibphoenix.com in pdf format.
Here's a quick solution (I think):
select rc.RDB$CONSTRAINT_NAME, iseg.RDB$FIELD_NAME,
iseg.RDB$FIELD_POSITION, frf.RDB$RELATION_NAME, frf.RDB$FIELD_NAME
from RDB$RELATION_CONSTRAINTS rc
inner join RDB$INDEX_SEGMENTS iseg on
(iseg.RDB$INDEX_NAME=rc.RDB$INDEX_NAME)
inner join RDB$REF_CONSTRAINTS ref on
(ref.RDB$CONSTRAINT_NAME=rc.RDB$CONSTRAINT_NAME)
inner join RDB$RELATION_CONSTRAINTS frc on
(frc.RDB$CONSTRAINT_NAME=ref.RDB$CONST_NAME_UQ)
inner join RDB$INDEX_SEGMENTS fis on
(fis.RDB$INDEX_NAME=frc.RDB$INDEX_NAME and
fis.RDB$FIELD_POSITION=iseg.RDB$FIELD_POSITION)
inner join RDB$RELATION_FIELDS frf on
(frf.RDB$RELATION_NAME=frc.RDB$RELATION_NAME and
frf.RDB$FIELD_NAME=fis.RDB$FIELD_NAME)
where rc.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
and rc.RDB$RELATION_NAME='table2'
order by rc.RDB$CONSTRAINT_NAME, iseg.RDB$FIELD_POSITION;
Just a note: each rc.RDB$CONSTRAINT_NAME is one foreign key. What
follows are the field name, its position, referenced table, and the
field name in the referenced table (referenced by the aforementioned
field name).

> and what's the system fields that show field type? as while i use
> RDB$FIELD_SOURCE it show either domain name or sth like RDB$13 ...
> how to get the field type like varchar(20)
> directly?

It's not quite straightforward, but you have the needed information in
the table RDB$FIELDS. You have found out correctly that
RDB$RELATION_FIELDS holds the list of fields of a given table, but their
definitions are listed in the RDB$FIELDS. You must get the
RDB$FIELD_SOURCE value of the wanted field from RDB$RELATION_FIELDS and
"plug" it into RDB$FIELD_NAME of RDB$FIELDS to get the field's
definition. My explanation is a bit awkward, so I'd again suggest to
read the IB6 documentation, there's a section on system tables somewhere
(I forgot where). Maybe this example would help you to understand it:
select rf.RDB$FIELD_NAME, rf.RDB$FIELD_POSITION, rf.RDB$NULL_FLAG,
f.RDB$FIELD_TYPE, f.RDB$CHARACTER_LENGTH, f.RDB$FIELD_SUB_TYPE,
f.RDB$FIELD_PRECISION, f.RDB$FIELD_SCALE, f.RDB$SEGMENT_LENGTH
from RDB$RELATION_FIELDS rf
inner join RDB$FIELDS f on (f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
where RDB$RELATION_NAME='table2'
order by rf.RDB$FIELD_POSITION;

f.RDB$FIELD_TYPE gives you the basic type of the field (blob, char,
double, integer, time, etc.). f.RDB$FIELD_SUB_TYPE gives you further
description of the field based on the f.RDB$FIELD_TYPE, eg. for blobs
it's the blob type, for char it will say whether it's binary data or
not, for smallint, integer, and int64 it will say whether it is what it
says it is or it is a numeric or a decimal field (taking a smallint, an
integer, or an int64 to represent). For decimals and numerics you may
also want to examine RDB$FIELD_PRECISION which stores the precision, and
RDB$FIELD_SCALE which stores negative scale (ie. multiply the value with
10 to the power of the scale - it gives you the position of the decimal
point). RDB$NULL_FLAG says whether the field is nullable,
RDB$CHARACTER_LENGTH gives you the maximum length for char and varchar
fields (eg. 20 for a varchar(20)), and RDB$SEGMENT_LENGTH gives you the
segment length for blobs.
The actual values for all those types are defined in the documentation.

HTH,
-AlesS