CODEWITHSUNDEEP
mysqlsql
Gabriela Dias
Gabriela Dias

Reputation: 349

delete a comment in a post with FOREIGN KEY

I have a post and comment structure, and I change it adding a foreign key:

posts:

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user` varchar(40) NOT NULL,
  `text` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

post's comments:

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `postid` int(11) UNSIGNED NOT NULL,
  `user` varchar(40) NOT NULL,
  `texto` varchar(3000) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`postid`) REFERENCES posts (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

SO, if an user wants to delete his post:

delete FROM posts WHERE id=? and user=?

and his post has comments mysql show me: cannot delete or update a parent row: a foreign key constraint fails.

my question is, is this posts/comments structure correct? should I use foreign key? how to delete this post if it has comments?

Upvotes: 1

Views: 314

Answers (1)

Andrei CACIO
Andrei CACIO

Reputation: 2129

It seems to me you need to set the cascade option. The cascade option deletes anything related to the FK when the row is deleted. This means that when a post is deleted all comments related to that post will be deleted as well.

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `postid` int(11) UNSIGNED NOT NULL,
  `user` varchar(40) NOT NULL,
  `texto` varchar(3000) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`postid`) REFERENCES posts (`id`)
     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

Upvotes: 0

Related Questions