dcsimg
www.webdeveloper.com
Results 1 to 3 of 3

Thread: Primary keys, indexes and auto deleting rows from multiple tables

  1. #1
    Join Date
    Apr 2012
    Posts
    21

    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
    Join Date
    Nov 2006
    Location
    Oakland
    Posts
    500
    story_id is a FOREIGN KEY in the "Characters" and "Animals" tables.
    However there is no direct relationship between the "Stories and the Comments tables. You therefore need to add a FOREIGN KEY story_id in the Comments table as well. It makes sense to have a given comment related to a story.

  3. #3
    Join Date
    Apr 2012
    Posts
    21
    thank you!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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
  •  
HTML5 Development Center



Recent Articles