Why are foreign keys more used in theory than in practice?

Foreign keys used in the manner you describe is not how they are meant to be used. They are meant to make sure that if a record logically depends on a corresponding record exist somewhere else, that that corresponding record is indeed there.

I believe that if developers/dbas have time to either (A) developer good names for their tables and fields, or (B) define extensive foreign key constraints, option A is the easy choice. I've worked in both situations. Where extensive constraints were relied upon to maintain order and keep people from screwing up things can really become a mess.

It takes a lot of effort to keep all your foreign key constraints up to date during development, time you could be spending on other high-value tasks that you barely have time for. In contrast, in situations where you have good naming conventions, the foreign keys are instantly clear. Developers don't have to look up foreign keys, or try a query to see if it works; they can just see the relationships.

I think foreign key constraints quickly become helpful as the number of different teams grow using a database grows. It becomes difficult to enforce consistent naming; knowledge of the DB becomes fragmented; it's easy for db actions to have unintended consequences for another team.

I've been programming for a couple decades, since well before relational databases became the norm. When I first started working with MySQL when I taught myself PHP, I saw the foreign key option and my very first thought was "Wow! That's retarded." The reason be only a fool believes that the laboratory dictates reality. It was obvious immediately that unless you were coding an application that would never, ever, be changed, you're wrapping your application in a steel cast where the only option is to either build more tables or come up with creative solutions.

This initial assessment has been born out in every single real-world production application I have come across. Not only do the constraints significantly slow down any and all modifications, they make the growing of the application almost impossible, something that is required for a business.

The only reason I've ever found for any constraints on a table is lazy coders not willing to write clean code to check data integrity.

DBMS's are built to allow the widest number of solutions while still working according to their core rules.

Restricting joins to defined foreign keys would limit functionality enormously, especially as most development does not occur with a dedicated DBA or review of SQL/stored procedures.

Having said that, depending on your Data Access Layer, you may be required to configure foreign keys, to use functionality. For example Linq to SQL.

I have always wondered why SQL doesn't have a syntax like

SELECT tbl1.col1, tbl2.col2
  FROM tbl1
  JOIN tbl2 USING (FK_tbl1_tbl2)

where FK_tbl1_tbl2 is some foreign key constraint between the tables. This would be incredibly more useful than NATURAL JOIN or Oracle's USING (col1, col2).

There is no way to set them up without a query in most MySQL GUI tools (Navicat, MySQL, etc.).