About like system (such as facebook like system) using myisam Mysql

Question

One problem I have them is 'like system' such as facebook like system.

I making the program using foreign hosting site having myisam mysql. as you know the myisam mysql has not transaction system. so it can not set key like foreign key to present relation or to set reference integrity

I think 'like system' what I making is important that how present and how set the reference integrity.

For instance, I made the A content and it has 3 'Likes'. if the contents delected, Like which already has also has to delected.

but int myisam mysql system it is impossible. because the isam mysql cannot set reference integrity system to tables

for these reason I regret the choice that I selected myisam mysql system at first step.

Even though I have know inno DB can these problems, I spend a lot of time to this project.

So how can I set reference integrity system to my project (Like system) or any other method to solve this situation?

Answer 1

If you can't change Database engine to Innodb and want to continue with MyISAM, triggers will be useful for you.

Syntax

CREATE TRIGGER trigger_name
BEFORE DELETE
  ON table_name FOR EACH ROW

BEGIN

  -- variable declarations

  -- trigger code

END;

Example

DELIMITER //

CREATE TRIGGER delete_likes
BEFORE DELETE
  ON tbl_content FOR EACH ROW

BEGIN

  DELETE FROM tbl_likes WHERE content_id = OLD.id;

END; //

DELIMITER ;

Lets say you have 2 tables named tbl_content and tbl_likes. Column content_id in tbl_likes is a reference to id column in tbl_content.

The sample trigger named delete_likes will get triggered before deleting a row in tbl_content and will delete related rows from tbl_likes.