I'm trying to build a simple tool to generate the generators for my
databases. I'm close to a solution, but that is not good enough.

Here is the code, the comment probably says what I am trying to do:

/* create the create generator statement for tables
where a column is part of the primary key for the table,
and is the only field in the primary key for the table,
and the column is an integer
and the column is not a foreign key
*/
select 'create generator g' || rtrim (ind.RDB$RELATION_NAME) || ';
from rdb$indices ind
join rdb$index_segments seg on seg.RDB$INDEX_NAME =
ind.RDB$INDEX_NAME
join rdb$relation_fields fields on
fields.rdb$field_name=seg.rdb$field_name and fields.rdb$relation_name
= ind.RDB$RELATION_NAME
join rdb$fields f on f.RDB$FIELD_NAME = fields.RDB$FIELD_SOURCE
left join rdb$relation_constraints con on (con.rdb$relation_name =
ind.rdb$relation_name and con.rdb$constraint_type like 'FOREIGN%')
where ind.RDB$INDEX_NAME like 'RDB$PRIMARY%'
and 1 = (select count (*) from rdb$index_segments s where
s.rdb$index_name=seg.rdb$index_name)
and 8 = f.RDB$FIELD_TYPE
and con.rdb$constraint_type is null
order by ind.RDB$RELATION_NAME


The problem, for me, lies in detecting when a primary key in one
table is actually a foreign key from another table. In the above
code, it is the two related lines:

left join rdb$relation_constraints con on (con.rdb$relation_name =
ind.rdb$relation_name and con.rdb$constraint_type like 'FOREIGN%')


and con.rdb$constraint_type is null


I've looked at the Language Reference pdf, chapter "Systems tables
and Views" till the electrons in the document became tired and
started dropping off my screen (or maybe it was my eyes.)

Any pointers appreciated.

Thanks

Bill