I have a table that references itself for one of its columns. This constraint is valid, as any entry in the table should indeed reference another entry going forward. However, what should I do for the very first entry? Note that it would be fine if the first entry would reference itself, but that does not seem to be possible. Is it reasonable to add the first entry to the table without the foreign key constraint, and then later apply this constraint for any future entries, or is there a more correct way of working around this issue?
CREATE TABLE worker (
id CHAR(25),
name VARCHAR(50) NOT NULL,
superior CHAR(25) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (superior) REFERENCES worker(id)
)Engine=INNODB;
INSERT INTO worker (id, name, superior)
VALUES (
"11111111-0922-1-111111111",
"Bob",
"11111111-0922-1-111111111"
);
Edit: This does indeed work as intended. My original table had an incorrect id format.