一种选择是使您的字段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)
否则,您仍然可以使用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)