Foreign Key Settings

Question

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

Answer 1

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:

• 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.

Rambling on...

SELECT ... FROM tableA JOIN tableB ON ...

delivers rows that show up in both tables (based on the ON condition).

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 NULLs.

The ON says how the tables are 'related'.

The WHERE clause, if present, filters out some of the rows.

Answer 2

Say you have a table of users and a table of photos and photos has a foreign key column referencing the user_id that corresponds to the unique ids of each user in the users table.

With ON DELETE CASCADE - when you delete a user when a photo is still referencing it, it will also the delete the photo.

With ON DELETE SET NULL - the row of the foreign key column of the photos table for the deleted user will be set to NULL as the users' unique id no longer existed because you deleted them.

So where would we want to use this? Well, this might be very useful if a user were to create some kind of resource and if for some reason you delete the user, but you still want to show the underlying photo. Maybe you need to delete a problematic user, but the posts might be meaningful so that might be a scenario where you want to hold the records around even if you deleted some other record that these things depend upon.