MySQL: Self-referential not null, auto_increment, foreign key?

Question

Is it possible in MySQL (in any version) to declare and use a table with a self-referential, not null, foreign key column referring to the AUTO_INCREMENT primary key of the same table? Essentially, I'd like to create the following table, the rows of which represent a forest of trees. I'd like the root nodes in the hierarchy to be indicated by the `parent_id` of the row being equal to the `id` (rather than allowing NULL in `parent_id` to indicate the root). e.g. ``` CREATE TABLE forest ( id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, parent_id BIGINT NOT NULL, CONSTRAINT fk_forest_parent_id FOREIGN KEY(parent_id) REFERENCES forest(id), name VARCHAR(20) NOT NULL UNIQUE ); ``` Creating this table works (5.0.44sp1-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)). However it does not appear to be possible to use LAST_INSERT_ID() to insert the `parent_id` of root nodes (which is not much of a surprise). The following doesn't work for example : ``` INSERT INTO forest(parent_id, name) VAL…

Accepted Answer

It's impossible to know what the `id` of a row is going to be before the `INSERT` completes, so calling `LAST_INSERT_ID()` when no `INSERT` has been done does not do anything. Instead you would need two separate statements (which could be run as a transaction in a stored procedure or what have you).

```
INSERT INTO forest(name) VALUES ("root 1");
UPDATE forest SET parent_id = LAST_INSERT_ID() WHERE id = LAST_INSERT_ID();
```

I don't think it makes sense for a root node to reference itself as a parent, though. Just saying.

MySQL: Self-referential not null, auto_increment, foreign key?

Solution

Problem