Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Primary keys, indexes and auto deleting rows from multiple tables

    I have about 4 tables in my MySql InnoDB database (members, stories, characters, comments). In my site people can post stories, add characters to each story, and make comments on each story.

    The tables are set up with the following columns:

    Members - member_id (primary key), email (index)

    Stories - story_id (primary key), member_id (index), the_story
    Characters - character_id (primary key), story_id (index), falsehood (index), story_character_name
    Animals - animal_id (primary key), story_id (index)
    Comments - comment_id (primary key), member_id

    So the problem I am having is when a member deletes a Story. I want the characters to also be deleted from the Characters table, and the pertaining comments to be deleted from the Comments table etc.

    So far this is only working on the Animals table. I think I know why it is not working on the Comments table - because I do not have story_id as an index, right?

    As for the Characters table, the column called falsehood (index) is no longer a column of the table (because I removed it, as it was something I was testing), however it remained under the structure, as an index. I left it there because I did not want it to mess anything up. Is that what is causing my problem on this table? Is it safe to delete it?

    The next big question is... once I get this working properly, will I have to go in and manually delete from Characters, Comments, the rows that no longer have a pertaining story_id?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    So far this is only working on the Animals table. I think I know why it is not working on the Comments table - because I do not have story_id as an index, right?
    Yes, but no. Whether a field is an index has nothing to do with the subject. What is important is that it is a FOREIGN KEY (and that you specify CASCADE DELETE) on that foreign key.

    It's perfectly legal and plausible to have a foreign key that is *not* indexed. The two are not tied together, at all.

    ******

    I don't understand how you can still have an index that refers to a non-existent column. Are you sure that index is still there???

    ******
    will I have to go in and manually delete from Characters, Comments, the rows that no longer have a pertaining story_id?
    No, I alluded to this above: You just need to specify ON DELETE CASCADE for the story_id foreign key. Look here:
    http://dev.mysql.com/doc/refman/5.1/...nstraints.html
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Juniper747 (09-04-2012)

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    That is great, thanks for everything!


  •  

    Tags for this Thread

    Posting Permissions

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