Foreign Key Delete/Update Strategy

Databases
1

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

Questions:

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

Agree?

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?

Thanks,

Debbie

2

When I clicked “Save”, phpMyAdmin displayed this…


SQL query:
ALTER TABLE `article_section` ADD FOREIGN KEY ( `article_id` ) REFERENCES `doubledee`.`article` (
`id`
) ON DELETE RESTRICT ON UPDATE CASCADE ;

ALTER TABLE `article_section` ADD FOREIGN KEY ( `section_id` ) REFERENCES `doubledee`.`section` (
`id`
) ON DELETE RESTRICT ON UPDATE CASCADE ;

That looks like what I want, but again, when I look at the GUI in phpMyAdmin, it shows “–” for ON DELETE?! :-/

Thanks,

Debbie

3

  1. disagree – deleting a row in the article_section table would have no effect on either articles or sections

  2. no idea – phpmyadmin is crap and i don’t use it, i never trusted it either

  3. if your primary keys are auto_increments, ON UPDATE CASCADE makes no sense at all

4

But if I decided to delete a “Section”, then it would delete the “article_section” record.

And when I formerly had “section” as a look-up table, deleting a Section (one)(parent) would delete the Article (many)(child) which would be catastrophic?!

  1. no idea – phpmyadmin is crap and i don’t use it, i never trusted it either

Why the animosity?!

  1. if your primary keys are auto_increments, ON UPDATE CASCADE makes no sense at all

Good point!

Debbie

5

if you say so

because phpmyadmin has inculcated really shítty database habits in countless developers

6

I say so.

If your Parent-Child is Customer–>Order, I think you’d know when you are about to trash an Order.

If your Parent-Child is FavoriteTypeOfIceCream–>Customer, I think it would be easy to delete “Rocky Road” and forget you are trashing ever Customer’s record that likes “Rocky Road”.

Maybe you would never make such a newbie mistake, but I could!!! :eek:

because phpmyadmin has inculcated really shítty database habits in countless developers

And those bad habits would be what?

And what, then, would you propose to make someone a better database developer/admin?

BTW, I don’t buy into the macho approach of, “I coded my entire website using NotePad” or “I built and manage my production database all by Command Line…” (I suspect you do…) :wink:

Debbie

7

nope, not me