Foreign Keys - What do they do for me?

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.creatorWHERE u.username = 'John Smith'

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.

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.