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?