Going through Bill Karwin book “SQL Antipatterns”, chapter 3, Naive Trees (adjacency table, parent-child relationship) there is an example for a comment table.
CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
parent_id BIGINT UNSIGNED,
comment TEXT NOT NULL,
FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);
Sample data
| comment_id | parent_id | comments
|------------| ----------|-------------------------------------
|1 | NULL |What’s the cause of this bug?
|2 | 1 |I think it's a null pointer
|3 | 2 |No, I checked for that
|4 | 1 |We need to check for invalid input
|5 | 4 |Yes,that's a bug
|6 | 4 |Yes, please add a check
|7 | 6 |That fixed it
The table has a comment_id, parent_id and a comment column. The parent_id is a foreign key referring to the comment_id.
The comment_id auto increment starting from 1.
Question.
If parent_id is supposed to be a foreign key which refers to the comment_id then how come the row with the comment_id = 1 have parent_id null/0 when the purpose of having a foreign key is to ensure referential integrity.
Note: I created the table as it is and tried entering the data and got this error
#1452 - Cannot add or update a child row: a foreign key constraint fails (`category`.`comments`, CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`comment_id`))