Foreign Key Delete/Update Strategy
I am working with MySQL in phpMyAdmin, and could use some advice on my Foreign Key settings.
Here is an example...
I have a many-to-many relations between Articles and Sections.
article -||------- |<- article_section ->0------||- section
When I am in "article_section" - which is an InnoDB table - I clicked on "Relation View" and I see fields which lets me set the Foreign Key relationships and constraints.
For ON DELETE, I chose "RESTRICT".
And for ON UPDATE, I chose "CASCADE".
1.) This weekend I had both set as "CASCADE" and then it occurred to me that this would be a bad idea in many cases. For example, if I have ON DELETE=CASCADE and I delete a record in a look-up table, then I could end up trashing an important record (e.g. Article, Member, etc.).
2.) If I choose DELETE=RESTRICT and save things, phpMyAdmin comes back up with "--"
Is that the same thing? (From my testing, it seems like it is, but I want to be sure!)
3.) Any other advice people want to share?