On Sun, Oct 29, 2023 at 7:08 PM Ivan Krylov via discuss < discuss@lists.mariadb.org> wrote:
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, ADD INDEX (id), ADD PRIMARY KEY (species_id, flag, wl_vac, id); Pantelis Theodosiou