• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1262
  • Last Modified:

Firebird schema query to retrieve foreign keys

We're trying to obtain the foreign keys for our Firebird database, but are having a bit of trouble finding the right SQL statement to do so.  Our last attempt is added below.

We have opened the database in MiTeC IB and can see the FK's and all the correct fields, but calling select * on RDB$DEPENDENCIES yields no results, so we're a little confused.

Thanks.


SELECT DISTINCT d2.*, r.RDB$CONSTRAINT_NAME AS CONSTRAINT_NAME,
                r.RDB$RELATION_NAME AS TABLE_NAME,
                d1.RDB$FIELD_NAME AS COLUMN_NAME,
                d2.RDB$DEPENDED_ON_NAME AS REFERENCED_TABLE_NAME,
                d2.RDB$FIELD_NAME AS REFERENCED_COLUMN_NAME
                FROM RDB$RELATION_CONSTRAINTS AS r
                LEFT JOIN RDB$REF_CONSTRAINTS refc ON r.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
                LEFT JOIN RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = r.RDB$RELATION_NAME
                LEFT JOIN RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME
                WHERE r.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' AND d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME
                    AND d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME

Open in new window

0
corebiz
Asked:
corebiz
  • 2
2 Solutions
 
NickUpsonCommented:
This may get you started, it's something I wrote a while ago to do something similiar.
 
what are you going to do with the list, there may be a better way to achieve it?

CREATE OR ALTER PROCEDURE PR_SYS_ARRANGE_TABLECOLS_NU 
returns (TAB_NAME Varchar(64),
         col_name Varchar(64))
AS 
 
begin
 
/*
  Purpose  : return a list of tables and the fields they use for indexes
*/
    for select R.RDB$RELATION_NAME
      from RDB$RELATIONS R
      where R.RDB$SYSTEM_FLAG = 0
      and R.RDB$VIEW_BLR is null
      into :tab_name
    do
    begin
 
        /* indexes including FK in alpha order */
        for select s.rdb$field_name
          from rdb$indices i
          join rdb$index_segments s on i.rdb$index_name=s.rdb$index_name
          where i.rdb$index_name not like 'RDB$%'   /* (exclude system indices) */
          and i.rdb$relation_name = :TAB_NAME
          order by 1 asc
          into :col_name
        do
        begin
            suspend;
        end
 
    end
end

Open in new window

0
 
corebizAuthor Commented:
Thanks Nick.
We have an open source solution called habanero which is an enterprise application framework. This ships with a tool Firstarter whch uses reverse engineering of the database to assist the developer in mapping the busienss objects and their relationships to the Database.
Habanero supports Firebird but Firestarter up unitl now has not allowed the reverse engineering of a firebird database.

We have sucessfully implemented reading the tables, columns and primary keys but are now trying to read the relationships between tables from the schema. To do this we need the source table, a list of foreign keys for the source table, for each foreign key the related (target) table, the columns in the source table and their related columns in the target table.

Our IDE (MiTeC IB) seems to be getting this information from somewhere. The table that seems to have this info is RDB$DEPENDENCIES but RDB$DEPENDENCIES seems to be empty for our database.

thanks
Eric
0
 
corebizAuthor Commented:
We've solved it.  It turns out that you can get the required info from the INDICES AND INDEX_SEGMENTS tables after all, because every FK is an index that points to an index.

Thanks for your help, which pointed us in the right direction.
SELECT source_index.RDB$RELATION_NAME AS TABLE_NAME,
                     source_index.RDB$INDEX_NAME AS CONSTRAINT_NAME,
                     source_segments.RDB$FIELD_NAME AS COLUMN_NAME,
                     target_index.RDB$RELATION_NAME AS REFERENCED_TABLE_NAME,
                     target_segments.RDB$FIELD_NAME AS REFERENCED_COLUMN_NAME
                    FROM RDB$INDICES source_index JOIN RDB$INDICES target_index ON source_index.RDB$FOREIGN_KEY=target_index.RDB$INDEX_NAME
                    JOIN RDB$INDEX_SEGMENTS source_segments ON source_index.RDB$INDEX_NAME=source_segments.RDB$iNDEX_NAME
                    JOIN RDB$INDEX_SEGMENTS target_segments ON target_index.RDB$INDEX_NAME=target_segments.RDB$iNDEX_NAME

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now