Reasons why you wouldn't use a foreign key? [php + MySQL]

Question

I'm working on an old web application my company uses to create surveys. I looked at the database schema through the mysql command prompt and thought the tables looked pretty solid. Though I'm not a DB guru I'm well versed in the theory behind it (having taken a few database design courses in my software engineering program).

That being said, I dumped the create statements into an SQL file and imported them in MySQL Workbench and saw that they make no use of any "actual" foreign keys. They'll store another table's primary key like you would with a FK but they don't declare it as one.

So seeing how their DB is designed the way I would through what I know (minus the FK issue) I'm left wondering that maybe there's a reason behind it. Is this a case of lazy programming or could you get some performance gains by doing all the error check programmatically?

In case you'd like an example they basically have Surveys and a survey has a series of Questions. A question is part of a survey so it holds it's PK in a column. That's pretty much it but they use it everywhere.

I'd appreciate any insight :) (I understand that this question might not have a right/wrong answer but I'm looking more for some information on why they would do this as this system has been pretty solid ever since we started using it so I'm led to believe that these guys knew what they were doing)

Answer 1

MySQL only supports the defining of actual foreign key relationships on InnoDB tables, maybe yours are MyISAM, or something else?

More important is that the proper columns have indices defined on them (so the ones holding the PK of another table should be indexed). This is also possible in MyISAM.

Answer 2

The original developers might have opted to use MyISAM or any other storage engine that does not support foreign key constraints.

Answer 3

You don't really have to use foreign keys.

If you don't have them, data might became inconsistent and you won't be able to use cascade deletes and updates.

If you have them you might loose some of the users data due to the bug in your SQL statements that happens because of schema changes.

Some prefer to have them, some prefer life without them. There's no real advantages in either case.

Answer 4

On uber large databases (the type that Teradata support) you find that they don't use Foreign keys. The reason is performance. Every time you write out to the database, which is often enough in a data warehouse you have the added overhead of having to check all the fk's on a table. If you already know it to be true, what's the point.

Good design on a small db would just mean you put them in, but there are performance gains to be had by leaving them out.

Answer 5

Here is a real life instance where I'm not using a foreign key.

I needed a way to store a parent child relationship where the child may not exist, and the child is an abstract class. Since the child could be of a few types, I use one field to name the type of the child and one field to list the id of the child. The application handles most of the logic.

I'm not sure if this was the best design decision, but it was the best I could come up with under the deadline. It's been working well so far!

Answer 6

As general points; keys speed up reads (if they are applicable to the read taking place they help the optimizer) and slow down writes (because they add overhead to the tables).

In the vast majority of cases the improvement of speed for reading and maintenance of referential integrity outweighs the minor overhead they add to writes.

This distinction has been blurred by cacheing, mirroring etc as so many reads on the very big sites don't actually hit the 'live' database - but this is not very relevant unless you are working for Amazon, Twitter or the like.