Self-Referential Table Fields In 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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

One option is to make your field NULL-able, and set the root record’s parent key to 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:

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)

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0