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

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?

That depends on whether you have cascade deletes set. Cascade deletes can be good, but you have to understand the ramifications thoroughly because one delete can cause "major" things to happen. But yes, if your goal is to delete a hierarchy of records with a single delete, foreign keys with cascading deletes is a viable option.

Answer 2

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.

Answer 3

If you are using foreign keys, you can specify to MySQL to perform a referencial action on deletion of the referenced record. For example, specifying "CASCADE" on a foreign key field will tell MySQL to delete any records that reference the primary key in the referred table. The "SET NULL" referential action will set the foreign key field to NULL when the record it refers to in the other table is deleted.

Answer 4

More than anything else, a Foreign Key is a referential constraint between two tables.

It can be used to achieve what you describe, if you have InnoDB tables with on delete cascade (see 13.6.4.4. FOREIGN KEY Constraints), but that is definitly not the first reason for which you should use foreign keys.

Answer 5

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?

Yes, this is a great use of Foreign Keys. The main objective of FKs is to maintain what is called 'referential integrity', basically, in your case, to make sure the user a certain table is refering to certainly exists. You can pick the action you want when deleting a user with the CASCADE property. You can choose if it will delete the rows that reference the FK, null the user_id on these rows or simply stop the delete query from happening.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

By the way, MyISAM does not support FKs, it will silently ignore them. You should use InnoDB for that.