Subject RE: [firebird-support] Re: Detect FK usage
Author Chad Z. Hower
:: Hi, I have a similar requirement in my Delphi app and my
:: solution was to create a procedure which i connect up to my
:: Dataset beforepost event.

We have a logic layer os I can control it there. The user does not have
direct access to our DB.

:: select i.rdb$relation_name as TableName, s.rdb$field_name as
:: FieldName from rdb$indices f inner join rdb$indices i on
:: (f.rdb$index_name = i.rdb$foreign_key) inner join
:: rdb$index_segments s on (s.rdb$index_name =
:: i.rdb$index_name) where f.rdb$relation_name = 'MYTABLE'

Need some time to look over this, but this looks about perfett for what I am
looking for.

:: so with this list i loop through each linked table and count
:: to see if any records are linked to the current record,

So this query lists all the tables that have FKs? But then I have to do a
select on each table in the list and filter on my FK to see if there are
items? Hmm could be quite CPU intensive.. But this operatino wont be
reqruested often.

:: so if i get a count of 1 or more it has obviously been used.
:: if this is useful i could send you some of my source for you
:: to adapt.

Hmm please. Im redoing it in a new framework and in C#, but I'll take a