MySQL中的自引用表字段

I have a table which has a "foreign key" referencing itself. This would be very useful, except I am uncertain how to add the first record to such a table. No matter what I add, I cannot provide a valid "foreign" key to the table itself, having no entries yet. Maybe I'm not going about this correctly, but I want this table to represent something that is always a member of itself. Is there a way to "bootstrap" such a table, or another way to go about self-reference?

我有一个表，其中有一个“外键”引用自己。这将是非常有用的，除了我不确定如何将第一条记录添加到这样的表。无论我添加什么，我都无法为表本身提供有效的“外来”密钥，但还没有条目。也许我没有正确地解决这个问题，但我希望这个表代表一直是自己的成员。有没有办法“引导”这样的表，或另一种方式去自我引用？

2 个解决方案

#1

One option is to make your field NULL-able, and set the root record's parent key to NULL:

一个选项是使您的字段为NULL，并将根记录的父键设置为NULL：

CREATE TABLE tb_1 (
   id       int   NOT NULL  PRIMARY KEY,
   value    int   NOT NULL,
   parent   int   NULL,
   FOREIGN KEY (parent) REFERENCES tb_1(id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.43 sec)

-- This fails:
INSERT INTO tb_1 VALUES (1, 1, 0);
ERROR 1452 (23000): A foreign key constraint fails.

-- This succeeds:
INSERT INTO tb_1 VALUES (1, 1, NULL);
Query OK, 1 row affected (0.08 sec)

Otherwise you could still use a NOT NULL parent key and point it to the root record itself:

否则，您仍然可以使用NOT NULL父键并将其指向根记录本身：

CREATE TABLE tb_2 (
   id       int   NOT NULL  PRIMARY KEY,
   value    int   NOT NULL,
   parent   int   NOT NULL,
   FOREIGN KEY (parent) REFERENCES tb_2(id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.43 sec)

-- This fails:
INSERT INTO tb_2 VALUES (1, 1, 0);
ERROR 1452 (23000): A foreign key constraint fails.

-- This succeeds:
INSERT INTO tb_2 VALUES (1, 1, 1);
Query OK, 1 row affected (0.08 sec)

#2

You could do:

你可以这样做：

SET FOREIGN_KEY_CHECKS = 0;

Then perform the insert and then, set it back to 1 after. It is a session variable though so a disconnect will reset it, and it will not affect other connections.

然后执行插入，然后将其重新设置为1。它是一个会话变量，因此断开连接将重置它，并且它不会影响其他连接。

#1