MySQL – Adding Primary Key to InnoDB Table

MySQLmysql-5.7primary-key

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?

Best Answer

You can have an auto-Incrementing column, as long as there is an index (key) on it, to fix this issue you need to execute those commands:

ALTER TABLE my_wonky_table ADD COLUMN count SMALLINT NOT NULL;
ALTER TABLE my_wonky_table ADD INDEX (count);
ALTER TABLE my_wonky_table CHANGE count count SMALLINT NOT NULL AUTO_INCREMENT;
ALTER TABLE my_wonky_table ADD PRIMARY KEY (id, count);
Related Question