How to delete all related records from different MySQL tables

Question

I have two tables in my database: 'stories' and 'votes'.
The stories table contains all information about an article (e.g. title, body, author name, etc.). The votes table contains all votes on all articles. There is a field in votes called item_name which contains the id of an article that a vote was cast on.

In simple words, item_name in votes is equal to id in stories (depending on which article a user voted on).

The question is: if an article gets deleted, how can I automatically delete all records in the votes table that are related to that article?

Can it be set up in the database itself, so there's no need to set up additional PHP queries?

Here is my database's structure:

stories

---

votes

Answer 1

MySQL has different storage engines. Default storage engine is MyISAM in most of MySQL managing IDEs. If you use this storage engine for your tables, you can not create any relation between their columns and you have to delete related columns by yourself.

For what you want, innoDB is the best solution. This type of storage engine make the situation to create relation between columns of different tables. You will need Primary Keys and Foreign Keys in your tables and after creating these relation, you must specify the features below to them:

ON UPDATE CASCADE and ON DELETE CASCADE

So you won't need to delete related columns values after deleting the main record.

Take a look at this link to compare them in a benchmark test.

Answer 2

Set up foreign keys on the related fields with cascade on delete.

Maybe someone will be capable of giving you a more detailed answer, but you have to use an engine that supports foreign keys (like InnoDB), and PHPMyAdmin should help you with the rest if you don't know how to do it by hand.

Simply put, setting cascade on delete on a field tells your database to delete every record that has that constraint when, in your case, the article (that's on another table) is deleted.

See here for more information:

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

http://forums.digitalpoint.com/showthread.php?t=488163