Primary keys, indexes and auto deleting rows from multiple tables

[Juniper747]

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?

[Old Pedant]

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

[Juniper747]

That is great, thanks for everything!