Subject RE: [firebird-support] Constraints - Cascade Delete from multiple parent tables
Author Alan McDonald
> Hi all
> I have tried but was not successful.
> - I have a transaction table.
> - This table receives entries (internally generated from the app) from
> multiple parent tables.
> - Any one record will definitely belong to one parent table.
> - The FK field name is iPID
> - This FK field combined with a cSource field (which contains a few
> identifying characters for each parent table like ! or @ and so on) will
> always provide a unique identification even if iPID contains same value
> - When a record is deleted in a parent table (PK = iID) I want it to
> cascade the delete in this transaction table
> Is this possible? If so please let me know how to make such constraints
> in the transaction table.

you cannot use the system FK constraint mechanism for this setup. You cannot
have a "multi-homed" foreign key. You can, however, use before delete
triggers on the parent table to delete records in your transaction table.
The only other way to use system cascading deletes in this situation is to
have separate transaction tables for each parent. You can always union the
tables to gether if you want result sets which combing them.

> Thanking you and wishing everybody a Happy New Year.
> --
> Regards
> Bhavbhuti