#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    98
    Rep Power
    4

    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. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by juniper747
    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?
    sorry, wrong

    it's because you don't have ON DELETE CASCADE specified for the foreign key

    note: an index is not a foreign key, but a foreign key has to have an index to work


    Originally Posted by juniper747
    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?
    no, it'll be automatic

    please do a SHOW CREATE TABLE for each table, and i'll help you with the foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    98
    Rep Power
    4
    Originally Posted by r937
    sorry, wrong

    it's because you don't have ON DELETE CASCADE specified for the foreign key

    note: an index is not a foreign key, but a foreign key has to have an index to work


    no, it'll be automatic

    please do a SHOW CREATE TABLE for each table, and i'll help you with the foreign keys



    Ok I really appreciate it, here you go. I cropped it down a little bit, but this should give you the idea... let me know how I can fix it. THank you!

    stories:
    CREATE TABLE `stories` ( `story_id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL, `the_story` varchar(255) NOT NULL, PRIMARY KEY (`story_id`), KEY `member_id` (`member_id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

    animals:
    CREATE TABLE `animals` ( `animal_id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL, `story_id` int(11) NOT NULL, PRIMARY KEY (`animal_id`), KEY `d_id` (`story_id`), CONSTRAINT `animals_ibfk_1` FOREIGN KEY (`story_id`) REFERENCES `stories` (`story_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

    characters:
    CREATE TABLE `characters` ( `tag_id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL, `d_id` int(11) NOT NULL, `contacts` int(11) NOT NULL, PRIMARY KEY (`tag_id`), KEY `tagmem_id` (`mem_id`), KEY `d_id` (`d_id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

    comments:
    CREATE TABLE `comments` ( `comment_id` int(11) NOT NULL AUTO_INCREMENT, `story_id` int(11) NOT NULL, `memberer_id` int(11) NOT NULL, `comment` varchar(2000) NOT NULL, PRIMARY KEY (`comment_id`)) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    to start with, your tables contain some errors and inconsistencies

    (perhaps you are showing fake tables/columns? and didn't obfuscate them completely?)

    for example, in the characters table, KEY `tagmem_id` (`mem_id`) is invalid because that table doesn't have a column called mem_id

    and there is no story_id in the characters table, so you'll be unable to add a character to a story

    for the other tables, here is how you would declare foreign keys --
    Code:
    ALTER TABLE stories
    ADD CONSTRAINT stories_ibfk_1 
          FOREIGN KEY ( member_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    
    ALTER TABLE animals
    ADD CONSTRAINT animals_ibfk_2 
          FOREIGN KEY ( member_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
            
    ALTER TABLE characters
    ADD CONSTRAINT characters_ibfk_1
          FOREIGN KEY ( member_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    
    ALTER TABLE comments
    ADD CONSTRAINT comments_ibfk_1 
          FOREIGN KEY ( story_id ) 
            REFERENCES stories ( story_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    
    ALTER TABLE comments
    ADD CONSTRAINT comments_ibfk_2 
          FOREIGN KEY ( memberer_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    i trust you will see the pattern here and be able to do the others yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    98
    Rep Power
    4
    Originally Posted by r937
    to start with, your tables contain some errors and inconsistencies

    (perhaps you are showing fake tables/columns? and didn't obfuscate them completely?)

    for example, in the characters table, KEY `tagmem_id` (`mem_id`) is invalid because that table doesn't have a column called mem_id

    and there is no story_id in the characters table, so you'll be unable to add a character to a story

    for the other tables, here is how you would declare foreign keys --
    Code:
    ALTER TABLE stories
    ADD CONSTRAINT stories_ibfk_1 
          FOREIGN KEY ( member_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    
    ALTER TABLE animals
    ADD CONSTRAINT animals_ibfk_2 
          FOREIGN KEY ( member_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
            
    ALTER TABLE characters
    ADD CONSTRAINT characters_ibfk_1
          FOREIGN KEY ( member_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    
    ALTER TABLE comments
    ADD CONSTRAINT comments_ibfk_1 
          FOREIGN KEY ( story_id ) 
            REFERENCES stories ( story_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    
    ALTER TABLE comments
    ADD CONSTRAINT comments_ibfk_2 
          FOREIGN KEY ( memberer_id ) 
            REFERENCES members ( member_id ) 
            ON DELETE CASCADE ON UPDATE CASCADE ;
    i trust you will see the pattern here and be able to do the others yourself

    Great, yes I did do some fake naming and removing additional columns that I have the I didn't want to confuse you with... so each one of those queries are a bit longer and named a bit differently than shown. Any how, I think I get the idea. But I have a couple questions before I start:

    -1) Those are individual queries that I should do one at a time right? Not one big query bundle?

    0) Also, what is the _ibfk_1 and _ibfk_2? Is this just a way to give a unique name to the cascading function?

    1) I noticed that on the animals table you are altering it even though the foreign key is already set to delete on cascade. So that would just alter what's there now right? I just want to make sure I wouldn't be duplicating anything.

    2) I see you added 2 foreign keys to the comments table. This makes sense because if someone deletes a story, you'd want all the comments deleted as well, and if someone deletes their membership, then you would want their comments to go as well- am I getting this right?

    So I'd probably want to add another foreign key between characters and stories, because if someone deletes a story, then all the characters that were tagged in that story should also be deleted.

    3) So what I need to do if find all the possibilities where when something from one table is deleted, so should something from another table, and then add these individual "ALTER" queries one at a time. Correct?

    4) Also would you have an answer to my original question (I thought it was weird that that this index was still there):
    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?
    4) I will definitely do a back up for db before I start anything, so that will be a good fail safe. Is there any special way that you would do this backup?

    Thanks, sorry for the 20 questions!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    -1) yes
    0) yes
    1) it's adding a different foreign key, not altering the one that's already there
    2) yes
    3) yes
    4) what index?
    4) mysqldump

    Comments on this post

    • juniper747 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    98
    Rep Power
    4
    That is great, thanks for everything!

IMN logo majestic logo threadwatch logo seochat tools logo