delete child records when deleting parent record

[suyesh.amatya]

I have two table structure as follows:

 

CREATE TABLE IF NOT EXISTS `blog` (

  `post_id` int(12) NOT NULL auto_increment,

  `date_of_post` datetime NOT NULL,

  `subject` longtext NOT NULL,

  `content` longtext NOT NULL,

  PRIMARY KEY  (`post_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

 

 

 

CREATE TABLE IF NOT EXISTS `commentblog` (

  `comment_id` int(12) NOT NULL auto_increment,

  `post_id` int(12) NOT NULL,

  `date_of_comment` datetime NOT NULL,

  `name` varchar(50) NOT NULL,

  `comment_content` longtext NOT NULL,

  PRIMARY KEY  (`comment_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

While deleting a record having specific post_id in the table blog,I want all the records in the commentblog

deleted as well.I have heard about cascade delete,can any one suggest me with the required query to do so...

 

Thanks

[JonnoTheDev]

You can either run 2 queries or use a foreign key constraint (you will need to alter the blog table) since you are using InnoDB tables. Read http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html