Way to check for foreign key references before deleting in MySQL?

Question

I'm working with a content management system, and users are prompted with a confirmation screen before deleting records. Some records are foreign key referenced in other tables, and therefore they cannot be deleted.

I would like to display a message beside a given record if it has foreign key references. To know whether I should display the message for a record, I could just query the referencing table and see if there are references. But the problem is, there are about a dozen tables with records potentially referencing this record, and a lookup could take a "long" time.

Is there an easy way to tell whether the record is delete-ready (ie. has no foreign key references)?

Answer 1

If you use DB Engine that supports transaction, then I think the easiest way without checking all the tables that might have a reference to a deleted record is:

1. Start a new transaction
2. Try to delete the requested record
3. If the record was deleted, than it can be deleted. If an error occurs, then it cannot be deleted, and you can display a message to the user.
4. Rollback the transaction