On Sun, 29 Oct 2023 18:12:53 +0200
Gordan Bobic <gordan.bobic@gmail.com> wrote:
> ALTER TABLE mol_trans
> DROP INDEX spid_flag_wl,
> ADD COLUMN id int unsigned auto_increment,
> ADD PRIMARY KEY (species_id, flag, wl_vac, id);
I wasn't able to add a primary key like this. My version of MariaDB
only allows the id column in the beginning of the compound primary key.
I can't comment on the performance issues but it is possible to have the auto generated id column as last in the PK index.
It only needs an index with id as the first column.
This should not cause an error:
ALTER TABLE mol_trans
DROP INDEX spid_flag_wl,
ADD COLUMN id int unsigned auto_increment,