MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time
I discuss the problem here, including a couple of workarounds.

The simple workaround is to have local_id be AUTO_INCREMENT and abandon the requirement for the values starting over at 1.

Keep in mind that ROLLBACK may cause you to lose ids.