Search code examples
firebird

How to get foreign key referenced table in Firebird


There is a table1 that has foreign key to table2 (one table2 to many table1). How to get list of table2 fields:

"table2.f1"
"table2.f2"
"table2.f3"

just by giving "table1.f3" (that has foreign key to table2). How to select this from system tables?


Solution

  • Lists all referenced by FK tables and fields.

    SELECT
        detail_index_segments.rdb$field_name AS field_name,
        master_relation_constraints.rdb$relation_name AS reference_table,
        master_index_segments.rdb$field_name AS fk_field
    
    FROM
        rdb$relation_constraints detail_relation_constraints
        JOIN rdb$index_segments detail_index_segments ON detail_relation_constraints.rdb$index_name = detail_index_segments.rdb$index_name 
        JOIN rdb$ref_constraints ON detail_relation_constraints.rdb$constraint_name = rdb$ref_constraints.rdb$constraint_name -- Master indeksas
        JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name
        JOIN rdb$index_segments master_index_segments ON master_relation_constraints.rdb$index_name = master_index_segments.rdb$index_name 
    
    WHERE
        detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY'
        AND detail_relation_constraints.rdb$relation_name = :table_name