I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this?
Current Table Scheme (1 Table):
CREATE TABLE `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent` int(11) DEFAULT NULL, `post` int(11) NOT NULL, `comment` varchar(255) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (`id`), KEY `child_idx` (`parent`,`id`), KEY `post_FK_idx` (`post`), CONSTRAINT `post_comment_FK` FOREIGN KEY (`post`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=latin1
- Should I add a second table for the child and do a
JOINwhen retrieving The comments?
- If child can be in 1 table, how should I delete all children when parent get deleted? (I have read about Foreign Key, but that doesn't work on 1 table.)
When I create a Foreign Key on 1 table, it works and get created, but when I try to enter data I get the following error.
Cannot add or update a child row: a foreign key constraint fails (`App`.`comments`, CONSTRAINT `child_comment_FK` FOREIGN KEY (`parent`) REFERENCES `comments` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)"
INSERT INTO comments SET parent = :parent, post = :post, comment = :comment;