I've inherited myself a fun table with some peculiar indexes (and no primary key) on it, that looks as follows:
CREATE TABLE `my_wonky_table` (
`id` bigint(20) unsigned NOT NULL,
`login` varchar(127) DEFAULT NULL,
`timestamp` int(10) unsigned NOT NULL,
`ip` varchar(32) CHARACTER SET ascii DEFAULT NULL,
`val_1` int(10) unsigned DEFAULT NULL,
`val_2` varchar(127) DEFAULT NULL,
`val_3` varchar(255) DEFAULT NULL,
`val_4` varchar(127) DEFAULT NULL,
`val_5` int(10) unsigned DEFAULT NULL,
KEY `my_wonky_table_id_idx` (`id`),
KEY `my_wonky_table_timestamp_idx` (`timestamp`),
KEY `my_wonky_table_val_1_idx` (`val_1`,`id`),
KEY `my_wonky_table_val_2_idx` (`val_2`,`id`),
KEY `my_wonky_table_val_4_idx` (`val_4`,`id`),
KEY `my_wonky_table_val_5_idx` (`val_5`,`id`),
KEY `my_wonky_table_ip_idx` (`ip`(16),`id`),
KEY `my_wonky_table_login_idx` (`login`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION pdefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
Now, because it has no primary key, nor a fully unique index, MySQL adds a GEN_CLUST_INDEX
(that's taking up quite some space, but that's a different matter).
Note that even though there is a column called id
, it's not actually a unique entry. For this reason, I want to add an (auto incremented) column, that, combined with the id
column, should act as the primary key.
However, I appear to be unable to add an auto_incremented column; I suspect this is because of the 'secret' column MySQL adds, because of the error message saying I already have an auto-incremented column, even though I don't believe I do.
The following:
ALTER TABLE my_wonky_table
ADD COLUMN count SMALLINT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id, count);
yields:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
Any ideas?