To use a foreign key in MySQL or not to?

Question

I have been coding with MySQL DBs for a couple years now and I have never used a foreign key. Now, I understand that a foreign key is like an ID on one table that corresponds with a primary key of another table. I have a user table on my site and probably around 10 other tables that all correspond with the primary key of my user table - however they are not set as foreign keys.

What am I missing out on by not having these 10 other tables have a foreign key? I mean, as far as I can tell they basically are a foreign key except they do not have that value saved/assigned to them in the DB.

Is there some other benefit here that I am just not aware of?

I realize too that a primary key cannot be null, but a foreign key can be. This will never be an issue in my case as my user table is created, and and when a new user is added to my user table I add their appropriate entry to the 10 other tables.

Answer 1

Adding foreign keys is always a good idea - at least I've never seen a compelling reason not to use them.

• enforces referential integrity (can't delete a parent if a child exists, can't insert orphans, or a child with an invalid parent id)
• works as an index
• With foreign keys, no matter how the data is accessed, whether through an app, an automated process, or someone without caffeine at the terminal the rules are uniformly enforced.

Answer 2

A FOREIGN KEY serves two purposes:

• It ensures that you relationships are always consistent at the cost of some checking overhead
• It (disputably) simplifies cascaded updates and deletes.

In most cases, this functionality can be more efficiently implemented using other tools.

Answer 3

With foreign keys you

• can make sure that only valid user_id's are put to those fields
• use cascades on delete easier don't
• don't have to manually define indexes on those fields (innodb)