6

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

enter image description here

votes enter image description here

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Ilja
  • 44,142
  • 92
  • 275
  • 498
  • Look into [ON DELETE CASCADE](http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html) if you have FK relationships set up. If you don't have an FK relationship set up you will need to delete the votes data manually. – Aaron McIver Dec 28 '11 at 18:01

2 Answers2

6

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.

Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127
  • Right now votes table is innoDB type, do both of them have to be of the same kind or will it work right ow? – Ilja Dec 28 '11 at 18:35
  • Also you need to create their relations, otherwise you can not achieve your goal just with setting them as InnoDB – Mohammad Saberi Dec 28 '11 at 19:10
  • Ok I've set them both to inno DB and was trying to set the relation. I can't figure out where to put ON UPDATE CASCADE and ON DELETE CASCADE. Do i create foreign key for item_name in votes table and do something there? – Ilja Dec 28 '11 at 20:33
  • You must have a story_id in your vote table. Assign it as a foreign key and create a relation with id in you story table. Then go ahead ... – Mohammad Saberi Dec 28 '11 at 20:39
  • I think item_name is what I thought you must have. It's better to use more appropriate names for your columns and this will help you more in your projects – Mohammad Saberi Dec 28 '11 at 20:43
  • How Do I add foreign key? Right now I changed item_name to story_id and I'm trying to edit it in phpMyAdmin, but I can't see anything saying foreign key or something. Sorry I might be looking in a wrong place, I'm really new to this. – Ilja Dec 28 '11 at 21:01
  • @IlyaKnaup, in phpMyAdmin go to your table and select the "Structure" tab, at the top. Once there, for each field of the table you have a "More" drop down box at the right, hover your mouse on it and select "Add Index"; do this to every field you want to create a foreign key. Now, still on the "Structure" tab of the table, and below the fields, you have a link named "Relation View", click it. You should now be able to select which fields are correlated and what happens `ON DELETE` and `ON UPDATE`. – Telmo Marques Dec 28 '11 at 21:12
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

Telmo Marques
  • 5,066
  • 1
  • 24
  • 34