Foreign Key Delete/Update Strategy

[DoubleDee]

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

[DoubleDee]

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

[r937]

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

[DoubleDee]

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

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?!

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

Why the animosity?!

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

Good point!


Debbie

[r937]

... which would be catastrophic?!

if you say so

Why the animosity?!

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

[DoubleDee]

if you say so

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!!!

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



Debbie

[r937]

(I suspect you do...)

nope, not me