Reputation: 349
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
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