1

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

Question:

  1. Should I add a second table for the child and do a JOIN when retrieving The comments?
  2. 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.)

Foreign Key:

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)"

Statement:

INSERT INTO comments SET 
    parent = :parent,
    post = :post,   
    comment = :comment;

Screenshot of Error:

enter image description here

0
1

Your code is:

INSERT INTO comments
SET parent = 0,
    post = 385,
    comment = 'Hello, World' ;

Do you have a row in comments with id = 0? If not, the error is what one would expect. Put parent=NULL, not parent=0, if you want a comment to not have a parent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.