[SOLVED] 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 table commentblog having same post_id

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

 

Thanks

[tibberous]

Like?

 

mysql_query("delete from `blog`, `commentblog` where `blog`.`post_id`='$id' and `blog`.`post_id`=`commentblog`.`post_id`");

 

 

[suyesh.amatya]

Sorry the query

DELETE FROM blog,

commentblog WHERE blog.post_id =3 AND blog.post_id = commentblog.post_id

gives an error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where blog.post_id=3 and blog.post_id=commentblog.post_id' at line 1

[tibberous]

=/

 

Might not be able to do it that way then, thought it might work like a join.

[suyesh.amatya]

Modifying the commentblog table structure to this gives the desired result.

 

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`),

  FOREIGN KEY (post_id) REFERENCES blog (post_id) ON DELETE CASCADE

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