How to delete all related records from different MySQL tables

Question

I have two tables in my database: " stories " and " votes ."
The history table contains all the information about the article (for example, title, body, author’s name, etc.). The table of votes contains all votes for all articles. There is a vote box called item_name that contains the id of the article in which the vote was made.

In simple words, item_name in votes equals id in stories (depending on which article the user voted on).

Question: if an article is deleted, how can I automatically delete all entries in the vote table associated with this article?

Can it be configured in the database itself, so there is no need to configure additional PHP queries?

Here is my database structure:

stories

---

of votes

Answer 1

MySQL has different storage mechanisms. MyISAM default storage engine in most MySQL drives the IDE. If you use this storage engine for your tables, you cannot create any relation between their columns, and you must delete the related columns yourself.

For what you want, innoDB is the best solution. This type of storage engine makes it possible to create relationships between columns in different tables. You will need primary keys and foreign keys in your tables, and after creating these relationships, you must specify the following functions:

ON UPDATE CASCADE and ON DELETE CASCADE

Therefore, after deleting the master record, you will not need to delete the corresponding column values.

Check out this link to compare them in a test case.

Answer 2

Configure foreign keys in associated fields with a cascade on deletion.

Someone might be able to give you a more detailed answer, but you should use a engine that supports foreign keys (e.g. InnoDB), and PHPMyAdmin should help you with the rest if you don't know how to do it manually.

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

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