Is this a job for Mysql Foreign Keys?

Question

In the past few years I have read plenty of articles on Foreign Keys in MySQL, nothing recent though. I know they are good to use on something like a forum topic that has child post's under it, if I delete a topic with 100 post's connected to that topic, foreign keys will make it delete those 100 topics for me, am I correct so far?

I'm working on a social network in php/mysql, there is a user table with a user ID then there is about 10 other tables that are connected to that table by that User ID field, should I be using foreign keys on this? I won't ever need to update all the tables but for example if a user deletes there account, in the past I would have the PHP run like 10 delete queries, 1 on each table for the user. Does this sound like a good job to be using Foreign Keys on?

Answer 1

Foreign keys don't do the work of deleting data. They ensure that you don't enter data into a field that isn't valid from the parent table. i.e. you wouldn't be able to enter a record with UserId 100 in a child table if you don't have a UserId 100 in the users table.

That being said using Cascading Deletes with a foreign key will do what you are asking, but be sure that is what you really want to happen. Do you really want all posts by a given user to be deleted if they delete their account, and all reply posts to the parent posts all the way down the thread.

You really need to know what you are doing to use cascading deletes properly and not really mess up your data.