SQL: auto_increment of primary key counts different since I added unique keys?

Question

Today I added two unique keys (external_id, name) to my table. Since then the counting of the id-column (primary key) is very weird and I'm not able to reproduce the issue.

I didn't delete any row, but I updated (ON DUPLICATE KEY). I'd like the primary key id to be counted up linear, like: 1, 2, 3, 4, 5, 6, ...

Structure:

CREATE TABLE IF NOT EXISTS `table_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `external_id` int(10) NOT NULL,
  `x` int(5) NOT NULL,
  `y` int(5) NOT NULL,
  `z` int(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `external_id` (`external_id`,`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

Content:

ID  | external_id | name | x | y | z
------------------------------------
  1 |          1  | A    | 3 | 3 | 2
  2 |          2  | B    | 2 | 2 | 5
  7 |          3  | C    | 5 | 3 | 2
 11 |          1  | D    | 7 | 6 | 3
 12 |          2  | E    | 5 | 4 | 2
 17 |          3  | F    | 3 | 8 | 5
 21 |          1  | G    | 6 | 6 | 3
 22 |          2  | H    | 8 | 5 | 7
 23 |          3  | I    | 1 | 0 | 9

Edit: The latest ID is 23. In the row statistics of PHPMyAdmin the next autoindex is announced as 27! What about 24 to 26? I'm confused.

Is there any wrong in the structure or a secret I haven't heard of? Thanks in advance!

Answer 1

Thanks for your replies. Finally I found the error. That's how the structure should look like:

CREATE TABLE IF NOT EXISTS `table_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `external_id` int(10) NOT NULL,
  `x` int(5) NOT NULL,
  `y` int(5) NOT NULL,
  `z` int(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`external_id`,`name`) # ----- this line was changed
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

Answer 2

If you have removed data from the table, the id values that have already been used will not be used again. So if you have:

ID  | external_id | name | x | y | z
------------------------------------
  1 |          1  | A    | 3 | 3 | 2
  2 |          2  | B    | 2 | 2 | 5
  3 |          3  | C    | 5 | 3 | 2
  4 |          1  | D    | 2 | 2 | 5
  5 |          2  | E    | 5 | 3 | 2
  6 |          3  | F    | 5 | 3 | 2

and then delete rows 3 - 6 and add more data, the id will start incrementing at 7 the next time around.