title: How to use an auto incremented primary key as a foreign key as well?
body: <p>This is what I'm trying to do:</p>
  
  <p>I have 2 tables...</p>
  
  <pre><code>CREATE TABLE `parent` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` text,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  
  CREATE TABLE `child` (
    `parent_id` int(11) DEFAULT NULL,
    `related_ids` int(11) DEFAULT NULL,
    KEY `parent_id` (`parent_id`),
    KEY `related_ids` (`related_ids`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>And then a constraint:</p>
  
  <pre><code>ALTER TABLE `parent` ADD FOREIGN KEY (`id`) REFERENCES `child` (`parent_id`);
  </code></pre>
  
  <p>As you can see the table parent has an auto-incremented primary key "id", which is also being used as a foreign key for the child table.</p>
  
  <p>Now I want to insert a record in the parent table, like this:</p>
  
  <pre><code>INSERT INTO parent SET DATA="abc";
  </code></pre>
  
  <p>And it fails with error: </p>
  
  <blockquote>
    <p>Cannot add or update a child row: a
    foreign key constraint fails
    (<code>myschema</code>.<code>parent</code>, CONSTRAINT
    <code>parent_ibfk_1</code> FOREIGN KEY (<code>id</code>)
    REFERENCES <code>child</code> (<code>parent_id</code>))</p>
  </blockquote>
  
  <p>I understand that it fails because it doesn't find a referred record in the child table. If I start by creating a record in the child table, set it's parent_id to 1, then reset the auto-increment counter of the parent table (so that the next insert will have id = 1), it works! But that's not a solution.</p>
  
  <p>I don't see the utility of the insert blocking if there is no related row in the child table...</p>
  
  <p>I'm just trying to do a one-to-many relationship...</p>
  
  <p>(I know I can use JOIN, but I'm trying to use table relations, for data integrity and also as metadata for PHP)</p>