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

What you're missing out on is enforced referential integrity (i.e. if your other table has user_id 27, there MUST be an ID 27 in the user table) and the ability to have automatic cascade updates and deletes (i.e. if you delete user 27, the corresponding rows in the other table are also automatically deleted, etc).

In my opinion, it's not worth it. I'm perfectly capable of having my code address referential integrity, and dealing with foreign keys is an enormous maintenance annoyance.

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)

Answer 4

In some databases (not sure about MySQL), a FOREIGN KEY automatically indexes, which nicely speeds up your joins and queries on foreign keys. Plus, there's the already-mentioned benifits of cascading deletes, referential integrity, etc.

Answer 5

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.