mysql - delete a comment in a post with FOREIGN KEY -
i have post , comment structure, , change adding 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 user wants delete post:
delete posts id=? , user=?
and post has comments mysql show me: cannot delete or update parent row: foreign key constraint fails.
my question is, posts/comments structure correct? should use foreign key? how delete post if has comments?
hmm seems me need set cascade
option. cascade
option deletes related fk when row deleted. means when post deleted comments related post deleted 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 ;
let me know if works.
cheers!
Comments
Post a Comment