0

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

0

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.

  • Thanks, that's very clear. Although I have already been using them, all on the no action setting. I am working on a project, hence asking the question, so would be interested in hearing about what the thoughts are on when one should use which setting and why. – zv426 Jul 24 '20 at 7:54
  • 1
    @portis123 - I have read thousands of questions here, and answered many of them. Newbies feel obligated to use FKs. Experienced users tend to use NO ACTION. Everyone gets into some kind of trouble -- usually because the order of creating tables and applying FKs is critical. (I started using databases before FKs existed; I am still not enamored with them. Caution: I may be the odd-ball.) – Rick James Jul 24 '20 at 16:16
  • @portis123 - "which setting and why" -- My opinionated answer is "none of them" and "take care of cascading (etc) yourself". (I'm surprised no one has come along to argue the other side.) – Rick James Jul 24 '20 at 16:19
  • @portis123 - I think, but am not sure: If you DELETE from the bridge table, no action should be taken on the other tables -- you are just removing a relation. If you DELETE from one of the other tables, it makes sense to DELETE from the bridge table. But no CASCADE. – Rick James Jul 24 '20 at 16:26
  • @portis123 - re "type of relationship" - A bridge table is (usually) a single "type". However, consider movies and persons: The bridge table could qualify the relationship as director, 'actor', etc. So there could be multiple rows for the same movie and person. The INDEXes should change accordingly; the FKs become too complex for to envision. – Rick James Jul 24 '20 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.