title: Why do I get a Duplicate Key Error for auto-incremented ID's that don't exist?
body: <p>i have a table structured like so</p>
  
  <pre><code> CREATE TABLE `person` (
     `uid` int(11) NOT NULL AUTO_INCREMENT,
     `email` varchar(150) NOT NULL,
     `pwd` varchar(50) NOT NULL,
     `first_name` varchar(45) DEFAULT NULL,
     `last_name` varchar(45) DEFAULT NULL,
     `is_active` tinyint(4) DEFAULT '1',
     `source` int(11) NOT NULL DEFAULT '1',
     `language_id` int(11) DEFAULT NULL,
     `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `modified_date` timestamp NULL DEFAULT NULL,
     `lastaccess_date` timestamp NULL DEFAULT NULL,
     `image_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`uid`),
   UNIQUE KEY `person_idx_1` (`email`),
   KEY `person_fk_1` (`source`),
   KEY `person_fk_2_idx` (`image_id`),
   CONSTRAINT `person_fk_1` FOREIGN KEY (`source`) 
     REFERENCES `person_source` (`uid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `person_fk_2` FOREIGN KEY (`image_id`) 
     REFERENCES `image` (`uid`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=200002 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>there used to be about 190000 records in this table. i just used a proc to delete (in reverse FK order) all users but 7 in this database to do some targeted testing. after the data trimming, i was unable to insert data into this table. I got the following error:</p>
  
  <pre><code>Error Code: 1062. Duplicate entry 'XXXXX' for key 'PRIMARY'
  </code></pre>
  
  <p>I was able to repair this by altering the auto increment for the table to 200000 (notice my <code>show create table</code> output was at 200002), which I knew would be after the last record prior to deleting the data.</p>
  
  <p>I get why my fix worked, but my question is what happened? Why did the auto-increment error appear when the records no longer existed in the table? Also, why wasn't the auto-increment value still at the original, higher value?</p>
  
  <p>Much thanks!</p>