I will start by mentioning that I am very new to databases. I am working in MySQL.
Can anyone give me a layman's view of when you would use which settings for the foreign key options so when you would use On Delete: Cascade, On Delete: Set Null, On Delete: No Action. Also, how would these work in the sense of foreign keys in a bridging table, does it depend on the context of the database as in what data it contains or is there a general rule of thumb as to which setting is best in this type of relationship?
If you can, please state it simply as I am not totally au fait with the jargon given I am a relative newbie. Thanks so much
Best Answer
It is possible to use databases without knowing anything about
FOREIGN KEYs
and not using them at all. I recommend you start that way.A
FOREIGN KEY
does three things: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.
Rambling on...
delivers rows that show up in both tables (based on the
ON
condition).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
NULLs
.The
ON
says how the tables are 'related'.The
WHERE
clause, if present, filters out some of the rows.