parent_id a foreign key(self reference) and null?

Question

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

Answer 1

Collecting some conclusions from the comments above in this CW answer.

• The parent_id is NULL in this table for a "root" node, which is at the top of the tree and therefore has no parent.

• Read https://dev.mysql.com/doc/refman/5.7/en/null-values.html: Be aware that the NULL value is different from values such as 0 for numeric types or the empty string for string types. For more information, see Section “Problems with NULL Values”.

• Also be aware that the keyword NULL is not the same thing as the literal string with the word 'NULL'.

• Read https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html: In MySQL SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.