Lookup foreign keys in Interbase/Firebird

Submitted by Frederic Marand on
Computing

The Paradox form system has an interesting peculiarity: any field which happens to be constrained by a foreign key within a native .DB Paradox data model automatically gets lookup values available for the user, but this is not available other database formats, whether they be natively supported within the BDE, or accessed using ODBC, so lookups have to be added in code.

The obvious mechanism for this is to build an ad-hoc query in a generic popup window, but this increases maintenance work, while it can be possible to create a generic query from the database schema. This is the query that works in Interbase 6.5 and Firebird 1.0x.

select
  RC.RDB$CONSTRAINT_NAME ConstraintName,
  RI2.RDB$RELATION_NAME MasterTable,
  RIS.RDB$FIELD_NAME MasterField
from
  RDB$RELATION_CONSTRAINTS RC
  left join RDB$INDICES RI1
    on RC.RDB$INDEX_NAME = RI1.RDB$INDEX_NAME
    left join RDB$INDICES RI2
      on RI1.RDB$FOREIGN_KEY = RI2.RDB$INDEX_NAME
        left join RDB$INDEX_SEGMENTS RIS
          on RI2.RDB$INDEX_NAME = RIS.RDB$INDEX_NAME
  where
        RC.RDB$RELATION_NAME   = 'TWHERE'
    and RC.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
order by
  RI1.RDB$RELATION_NAME,
  RC.RDB$CONSTRAINT_NAME,
  RI2.RDB$RELATION_NAME,
  RIS.RDB$FIELD_POSITION

This query returns the list of foreign keys in the form of triplets:

  • Constraint name : the name of the FK rule in the schema
  • Master table : the table holding the master field
  • Master field : the master field for the relation

From there, it is possible to