Subject RE: [firebird-support] Pre-testing delete for referential integrity issues
Author Alan McDonald
> I'm wondering if there is a 'best practice' for doing this. I have a
> database full of contact records. Of these, many have related tables
> of
> data that I can't delete the contact until those related records are
> re-assigned to other parties. Consequently a Cascade delete rule won't
> work here.
> But rather than giving the user the ability to 'try' to delete the
> record and be told that they can't because of a violation of a
> referential integrity rule, I'd like to be more pro-active with this
> and
> only give them a delete option of the contact record has no related
> data
> that would stop a delete from occuring.
> Is there is a good and fast way to determine if one of these records
> could be deleted other than giving the user the ability to try and then
> be told they can't? I'm basically trying to simplify their world a bit
> by being proactive and telling them up-front that a record can or
> cannot
> be deleted as it stands.
> All suggestions, comments, flames, etc. are actually greatly
> appreciated.
> Myles

if there's a foreign key, then a simple subselect count(*) from childtable
will provide a field value of either 0 or >0.
if it's zero you can enable the delete option, if >0 the delete option is
not enabled.
No round trip to the server for this option.