Add a second auto-increment field and allow duplicates

Question

I have an EXISTING table in which there is a primary key with an identifier and 6 more fields related to invoicing. I need to insert values ​​from an old table and paste all the values ​​into a new but newly created table. The old table shows the account numbers, and sometimes the account numbers have duplicates. I need this new column that I am trying to create, called invoice_id in AUTO_INCREMENT, when there is no value inserted for future values ​​to be inserted and ALLOW DUPLICATES to existing values ​​AND future values. When a value is not inserted, it needs to perform auto-detection.

 ID (primary) || invoice_ID (needs to auto_increment AND allow duplicates) || other colums 1 || 1 2 || 2 3 || 2 4 || 3 

I tried several commands, and here is what happens:

 ALTER TABLE `invoices` ADD `invoice_ID` INT NOT NULL AUTO_INCREMENT AFTER `ID` , ADD PRIMARY KEY ( `facture` ) 

RESULT:

 MySQL said: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key 

ALSO EXCLUDED:

 ALTER TABLE `invoices` ADD `invoice_ID` INT NOT NULL AUTO_INCREMENT AFTER `ID` , ADD KEY ( `invoice_ID` ) , ADD INDEX ( `invoice_ID` ) 

RESULT:

 #1075 - Incorrect table definition; **there can be only one auto column** and it must be defined as a key 

I also tried several different options, for example, without adding as a primary key, but it seems that as soon as I add an auto_increment request, it makes my request "AS PRIMARY KEY".

Answer 1

You can do this with a trigger. Here is an example.

So you have an old table:

 drop table if exists invoices_old; create table invoices_old ( invoice_ID int, another_column int ); insert into invoices_old values (1,11), (2,12), (2,13), (3,14), (4,15), (5,16), (6,17), (6,18), (7,19); 

which you want to insert into a new table:

 drop table if exists invoices_new; create table invoices_new ( id int not null auto_increment, invoice_ID int default null, /*it important here to have a default value*/ another_column int, primary key (id) ); 

Copy your data like this:

 insert into invoices_new (invoice_ID, another_column) select invoice_ID, another_column from invoices_old; 

Now that you have the data in the new table, you create a trigger in the new table to simulate the auto_increment column.

 drop trigger if exists second_auto_inc; delimiter $$ create trigger second_auto_inc before insert on invoices_new for each row begin set @my_auto_inc := NULL; select max(invoice_ID) into @my_auto_inc from invoices_new; set new.invoice_ID = @my_auto_inc + 1; end $$ delimiter ; 

Now when you insert more rows in a new table

 insert into invoices_new (another_column) select 20 union all select 21 union all select 22; 

and look at the table

 select * from invoices_new; 

it works.

Result:

 id invoice_ID another_column 1 1 11 2 2 12 3 2 13 4 3 14 5 4 15 6 5 16 7 6 17 8 6 18 9 7 19 16 8 20 17 9 21 18 10 22 

You are probably wondering why in the real auto_increment column, the identifiers jump from 9 to 16. Recently, there has been a good article about this, but I can not find it right now. In any case, you have nothing to worry about. Auto_increment exists to provide uniqueness, not gapless consistency.