Way to check for foreign key references before deleting in MySQL?
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 c开发者_Go百科ould 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)?
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:
- Start a new transaction
- Try to delete the requested record
- 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.
- Rollback the transaction
精彩评论