SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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


    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. #2
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    When I clicked "Save", phpMyAdmin displayed this...

    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ... which would be catastrophic?!
    if you say so


    Quote Originally Posted by DoubleDee View Post
    Why the animosity?!
    because phpmyadmin has inculcated really shítty database habits in countless developers
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    (I suspect you do...)
    nope, not me
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •