It is possible to use databases without knowing anything about
FOREIGN KEYs and not using them at all. I recommend you start that way.
FOREIGN KEY does three things:
- It is a constraint. It will slap your hand if you try to link from one table to another without there being an appropriate row. Think of it like filling out a form that calls for your home address, but you mis-type it and there is no house at the address you typed. If you never make typos, you don't need that check.
- It provides an index (aka "key") to make the above check. But you can create indexes as needed without having FKs. And if your tables are only a few hundreds of rows each, you might not notice that you are missing the significant performance benefits of indexes. An index is like a phonebook. (If you are too young to know what a phonebook is, visit a museum.)
- It can "cascade" things. Don't use that; just do it yourself. I'm referring to the deletion of a row in one table automatically causing the deletion of a corresponding row in another table. Analogy: When a person dies, the DMV, the voter registration, the bank, etc, need to be notified manually so they can delete that person from their lists. FKs facilitate having that happen automatically.
Since you mentioned a bridging table, I'll ramble on in that direction. (It sounds like you may have already mastered it.) There are 3 types of "relations" between "entities":
1:1 -- Don't use that. Why bother having two tables that march in lock step when one would do. (There exceptions; but that comes in another lecture.)
1:many -- This is simply implemented with a link ("id") in one table to allow "JOINing" to another table. Many rows of the first table link to a single row in the second table.
many:many -- This has many names, "bridging" on one of them. In the college example: students:classes, classes:teachers, etc. Bridging requires an extra table. See this for the optimal indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
Any of those mappings can degenerate to 1 or to 0.
SELECT ... FROM tableA JOIN tableB ON ...
delivers rows that show up in both tables (based on the
SELECT ... FROM tableA LEFT JOIN tableB ON ...
delivers all the rows of table A, whether are not there is a matching row in table B. If none exists, then the columns that should have come from tableB will be
ON says how the tables are 'related'.
WHERE clause, if present, filters out some of the rows.