Subject Re: Detect FK usage
Author Terry kahler
--- In, "Chad Z. Hower"
<chad-jm@h...> wrote:
> :: >But for edit - how can I determine easily if its already in use and
> :: >then decide to allow it to be updated or not? I don't want
> :: to hard code
> :: >checks against every table as this will lead to bugs in the
> :: application
> :: >code in the future as more tables are added.
> ::
> :: The referential integrity checks take care of this.
> Only if it affects the keys. The edits would be on fields that are
not part
> of the key. For exampel I have an Item table, its key is ItemID. It
has a
> Name field - but I don't wat the user to be able to edit Name after
> is referenced from another table. But since I don't want to code into my
> program all the tables that iw ould have to check first - I want to
> be able to detect automatically so that there wont be forgotten
tables that
> new FKs have been added to.

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.

basically it determines the Tablename from the SQL in the Dataset and
the current primary key value, then i get the TableNames and
FieldNames of all the Table that are connected by constraints to the
Dataset's Table,

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'

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

select count(*) from LinkTable where LinkField = 'MyCurrentValue'

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.

cheers, Terry