Foreign Keys - What do they do for me?

Question

I'm building a small application and setting up foreign key relationships between tables. However I'm confused as to WHY I really need this? What is the advantage - does it assist me when writing my queries that I don't have to perform any joins? Here's an example snippet of my database:

+-------------------+
| USERS             |
+-------------------+
| user_id           |
| username          |
| create_date       |
+-------------------+

+-------------------+
| PROJECTS          |
+-------------------+
| project_id        |
| creator           |
| name              |
| description       |
+-------------------+

There is a key relationship between users.user_id and projects.creator

Would I be able to perform a query like so?

SELECT * FROM PROJECTS WHERE USERS.username = "a real user";

Since MySQL should know the relationship between the tables? If not then what is the real function of Foreign keys in a database design?

Solution 1

Foreign keys provide referential integrity. The data in a foreign key column is validated - the value can only be one that already exists in the table & column defined in the foreign key. It's very effective at stopping "bad data" - someone can't enter whatever they want - numbers, ASCII text, etc. It means the data is normalized - repeating values have been identified and isolated to their own table, so there's no more concerns about dealing with case sensitivity in text... and the values are consistent. This leads into the next part - foreign keys are what you use to join tables together.

Your query for the projects a user has would not work - you're referencing a column from the USERS table when there's no reference to the table in the query, and there's no subquery being used to get that information before linking it to the PROJECTS table. What you'd really use is:

SELECT p.*
   FROM PROJECTS p
   JOIN USERS u ON u.user_id = p.creator
WHERE u.username = 'John Smith'

Solution 2

Basically, they won't give you any more functionality. They stop any inserts or updates breaking the referential integrity of your data model.

Another key aspect in my opinion is that they communicate the data model to any other developers using it. I have often looked at what foreign keys a table has to see how the data model fits together at a glance.

Solution 3

If you never do joins, you don't need foreign keys.

Come to think of it, if you never do joins, you don't need a relational database! (little joke) Seriously, if you have more than one table, you'd better learn how to use joins, and how to design foreign keys into a schema.

As previous responders have said, foreign keys enforce referential integrity. Without referential integrity, joins produce mysterious results.

---

My earlier response failed to note the real question behind the question. The question I answered was "why are there foreign keys in SQL schemas", and the answer is "in order to do joins". But rereading the the question, I'm understanding a much more subtle question, which is "why can't SQL do the joins for me, if it knows the linkages". This is a really good question. It deserves a better answer than the above.

A language where the engine supplies the join conditions is possible. One need only look at the graphical query design tool in Microsoft Access. Once one has declared all the intertable relationships to Access, one can pull data from multiple tables without specifying the join conditions all over again. Access figures them out automatically.

If one builds a two table query in Access, and then switches to SQL view, one will see that Access has in effect created a join with a join condition. Such a capability is possible in character based languages as well, but SQL is not such a language.

I note in passing that many projects can belong to one user. So Users is the "reference table" in the above schema, not Projects. I expect the easier automatic navigation direction would be automatic lookup from a reference table, not the other way around.

Solution 4

Using a foreign key constraint can provide the following:

• Prevent the database containing inconsistent data by preventing mismatched keys
• Prevent the database containing inconsistent data by automatically deleting orphaned rows (with ON DELETE CASCADE)
• Serve to document to future developers which column is a foreign key to which

Of course it's not mandatory to do any of those things, but likely to improve code quality over time. Being strict about thing is usually good - it leads to more errors in testing (and hence fewer in production)

View more solutions

Comments

[Marco Ceppi]

I've setup plenty of database designs without creating any foreign keys and they have used a one to many relationship. I want to know what Foreign keys do for me that I can't do without them.

[EddieC]

Given your table layouts above, What would your SQL statement be to get all the users who are part of project_id=1?

[OMG Ponies]

@Marco Ceppi: While you've modelled relationships without using foreign keys, there's nothing to stop a user from entering data that is not related into the column(s) that represent that relationship.

[Jani Hartikainen]

So which part of this answers the question here?

[Marco Ceppi]

Thank you - I figured I could get a way from Joins with this - but you answer helps describe exactly what this does.

[OMG Ponies]

I'd say the data validation is functionality - a foreign key constraint is like a CHECK constraint on steriods. CHECK constraints only validate the data; Foreign keys mean you can add associated information to that relationship--in addition to the data validation.

[Walter Mitty]

Years later, I have noticed that many people use the term "foreign key" to refer only to a column that has a declared foreign key constraint. I'm not sure whether the OP meant that. When I learned this stuff, years earlier, a foreign key was a foreign key, whether it was declared as such or not. Undeclared foreign keys are an invitation to broken referential integrity. This eclipses any performance differences there might be.

[philipxy]

"Without referential integrity, joins produce mysterious results." That is unsupportable. Every join has a meaning. Constraints don't need to be known to query. When they hold, some expressions always return the same result as other queries that wouldn't otherwise. Whatever you're trying to say by that, you're not saying it, and it is misleading. I see that that ended your original response, to which was added the horizontal rule & the rest of this answer. Adding to something unclear and/or misleading doesn't make it clear & doesn't undo its damage. And what was added doesn't correct it.

[Walter Mitty]

I'm not sure what you're driving at.

[philipxy]

Joins never "produce mysterious results"--whatever that means. And my last comment explains that referential integrity constraints need not hold, be known or be declared in order to query, and if they do hold they don't change the meaning of any query, just some queries then mean the same thing.

[Walter Mitty]

If you don't know what it means, how do you know that it is false?

[toraman]

Kind of a disappointment that it doesn't really help that much when soft deleting.