How to delete rows in table when PK is referenced in another table
Before data insertion, updates, deletes etc, database management systems that support foreign keys (like MySQL) make sure you do not introduce any inconsistencies. So, you need to make sure you’re not leaving orphan records behind in child tables.
The issue only arises when parent tables (where you want to delete records from) have corresponding records in the child tables and you want to delete one or more such records in parent table.
There are multiple solutions and workarounds to this issue. However, I strongly suggest that you rethink your database design as well.
In addition to that, you must be careful when you are making such changes. With other standard best practices and precautions, I suggest taking full backups and testing thoroughly in a production-like test/lab/dev environment first.
Finally, here is a list of some of the workarounds and solutions:
- This is the most obvious one ie. you can delete corresponding child records first and then delete records from parent table. This way your data should remain in a consistent state.
- Disable foreign key checks (SET foreign_key_checks = OFF;) in MySQL then delete records. However, it will leave orphan records (ie. data inconsistency in database) and might cause issues in the future.
- Use "referential actions" feature for foreign keys (in MySQL). See 184.108.40.206 Using FOREIGN KEY Constraints for example. This could help you automatically delete records from child tables.
- You can also insert a dummy (catch-all) record in the parent table and point all child records to this dummy record's PK ID (in parent table). This way MySQL will not complain when you're deleting parent table's records because they won't have any linked records in child tables (as the child records will be pointing to the dummy record now).
I recommend rethinking about your schema design as well.
Did this tutorial help a little? How about buy me a cup of coffee?
Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.