title: MySQL: Self-referential not null, auto_increment, foreign key?
body: <p>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?</p>
  
  <p>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 <code>parent_id</code> of the row being equal to the <code>id</code> (rather than allowing NULL in <code>parent_id</code> to indicate the root).</p>
  
  <p>e.g.</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>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 <code>parent_id</code> of root nodes (which is not much of a surprise).  The following doesn't work for example :</p>
  
  <pre><code>INSERT INTO forest(parent_id, name) VALUES (LAST_INSERT_ID(), "root 1");
  </code></pre>
  
  <p>The following does work, but is no longer making use of the auto increment:</p>
  
  <pre><code>INSERT INTO forest(id, parent_id, name) VALUES (1234, 1234, "root 1");
  </code></pre>
  
  <p>Is there a way to use a table defined in this way whilst still relying on the automatically generated PK, and whilst also retaining all of the constraints (NOT NULL, and FOREIGN KEY)?</p>
  
  <p>UPDATE - POSSIBLE SOLUTIONS:</p>
  
  <pre><code>START TRANSACTION;
  SET FOREIGN_KEY_CHECKS = 0;
  INSERT INTO forest(parent_id, name) VALUES (0, "root X");
  UPDATE forest SET parent_id = LAST_INSERT_ID() WHERE id = LAST_INSERT_ID();
  SET FOREIGN_KEY_CHECKS = 1; /* This seems to be important. */
  COMMIT;
  </code></pre>