主页 / 问题 / 606968
Accepted
YoFi
Asked:2020-12-23 16:55:03 +0000 UTC2020-12-23 16:55:03 +0000 UTC

FK Firebird 2.5 搜索实现

  • 772

我需要显示所有相互依赖的表的名称。有 200 个这样的表 + 从属表。例如，表 X 具有来自 Y 的 FK，Y 具有来自 Z 的 FK。即，查找 FK。是否可以从表列表和找到的表中实现 FK 查找？

这是一个查找一个表 (M_KONTRAH_GL) 名称的查询：

SELECT
   master_relation_constraints.rdb$relation_name AS reference_table
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 
   JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name
WHERE
   detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY'
   AND detail_relation_constraints.rdb$relation_name = 'M_KONTRAH_GL'
sql

1 个回答

  1. Best Answer
    SELECT
    detail_relation_constraints.rdb$relation_name AS base_table,
    master_relation_constraints.rdb$relation_name AS reference_table
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 
    JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name
WHERE
    detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY'
AND detail_relation_constraints.rdb$relation_name 
    IN ('table1','table2', .. ,'tableN')
ORDER BY 1,2
    • 1

相关问题