parent_id外键(自引用)，是否为null? [英] parent_id a foreign key(self reference) and null?

问题描述

翻阅Bill Karwin的书"SQL Antipatterns"，第3章，朴素树(邻接表，父子关系)，其中有一个注释表的示例.

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

样本数据

| 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

该表具有comment_id，parent_id和comment列. parent_id是引用comment_id的外键.

The table has a comment_id, parent_id and a comment column. The parent_id is a foreign key referring to the comment_id.

comment_id从1开始自动递增.

The comment_id auto increment starting from 1.

问题.

如果假定parent_id是引用comment_id的外键，那么当具有外键的目的是确保引用完整性时，带有comment_id = 1的行的parent_id null/0为何.

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-无法添加或更新子行:外键约束失败(类别" .comments`，CONSTRAINT"comments_ibfk_1"外键("parent_id")参考"comments"(`comment_id`))

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

推荐答案

在此CW答案中，从上面的评论中收集了一些结论.

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

• 在此表中，对于根"节点，parent_id是NULL，该节点位于树的顶部，因此没有父级.

• 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.

阅读 https://dev.mysql .com/doc/refman/5.7/en/null-values.html :请注意，NULL值不同于数字类型的值，例如0或字符串类型的空字符串.有关更多信息，请参见" NULL的问题值".

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

还请注意，关键字NULL与带有单词'NULL'的文字字符串不同.

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

阅读 https://dev .mysql.com/doc/refman/5.7/en/numeric-type-overview.html :在MySQL中，SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名.

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.

这篇关于parent_id外键(自引用)，是否为null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！