SQL: auto_increment of primary key counts different since I added unique
keys?
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
Is there any wrong in the structure or a secret I haven't heard of? Thanks
in advance!
No comments:
Post a Comment