> Get in amongst the following tables:
> RDB$REF_CONSTRAINTS - has the names of the foreign key
> constraints and the
> corresponding unique constraints that they reference
>
> RDB$RELATION_CONSTRAINTS shows the constraint names for all
> the tables (RI
> and other constraints) along with the index names
>
> RDB$INDEX_SEGMENTS shows you the makeup of all the indexes,
> including those
> used to enforce constraints.

Thanks Helen!
I grabbed ib_sql, and nutted out the foreign key linking.
Here's what I came up with, so other people don't have to work it out for
themselves!...

--- Begin Example Schema ---

create table tbl_farside (
int_siteid INTEGER NOT NULL,
str_word varchar(100) not null,
PRIMARY KEY(int_siteid,str_word)
);

create table tbl_nearside (
int_siteid INTEGER NOT NULL,
int_id INTEGER NOT NULL,
str_word varchar(100),
PRIMARY KEY(int_siteid,int_id),
FOREIGN KEY(int_siteid,str_word) references tbl_farside (int_siteid,
str_word) on update cascade
);

--- End Example Schema ---


/*
This finds all the far end foreign key info for each table.
Put table names in the 'NearRC.rdb$relation_name' to get info
for a specific table
Also handles composite primary and foreign keys
*/


SELECT NearRC.rdb$relation_name AS NEAR_TABLE,
NearIND.rdb$field_name AS NEAR_FIELD,
FarRC.rdb$relation_name AS FAR_TABLE,
FarIND.rdb$field_name AS FAR_FIELD
FROM rdb$index_segments FarIND
JOIN rdb$relation_constraints FarRC
ON FarIND.rdb$index_name = FarRC.rdb$index_name
JOIN rdb$ref_constraints RFC /* The pivotal piece of the query...*/
ON FarRC.rdb$constraint_name = RFC.rdb$const_name_uq
JOIN rdb$relation_constraints NearRC
ON RFC.rdb$constraint_name = NearRC.rdb$constraint_name
JOIN rdb$index_segments NearIND
ON NearRC.rdb$index_name = NearIND.rdb$index_name
WHERE NearRC.rdb$relation_name not like 'RDB$%' /* skip system tables */
AND NearIND.rdb$field_position = FarIND.rdb$field_position; /* Ensure index
columns match */


Feel free to put this on the firebird web site - good for reporting tools,
etc!

Nige.