[Sugestão] Limitação AUTO-INCREMENT em tabelas InnoDB

Gostaria de salientar uma particularidade a respeito do auto-increment, nesta aula o instrutor mostra que é possível forçar o auto-increment para o valor 100, daí novos registros inseridos seguirão a sequência a partir desse valor (101,102,103...), porém não é possível reiniciar o contador para um valor abaixo ou igual do último índice(ID) já inserido na tabela, por exemplo:

CREATE TABLE TAB_IDENTITY (ID INT AUTO_INCREMENT, DESCRITOR VARCHAR(20), PRIMARY KEY(ID));

INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE1');
INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE2');
INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE3');
INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE4');

SELECT * FROM TAB_IDENTITY

Então agora iremos forçar o auto-increment para 100:

INSERT INTO  TAB_IDENTITY (ID, DESCRITOR) VALUES (100,'CLIENTE100');

SELECT * FROM TAB_IDENTITY

Agora iremos adicionar 3 registros sem forçar o ID para vermos como fica:

INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE101');
INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE102');
INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE103');

SELECT * FROM TAB_IDENTITY

Note que o auto-increment continuou a contagem a partir daquele do ID 100 que forçamos anteriormente, porém vamos supor que agora queremos popular a tabela a partir do ID 5:

INSERT INTO  TAB_IDENTITY (ID, DESCRITOR) VALUES (5,'CLIENTE5');

SELECT * FROM TAB_IDENTITY

Olha só, de fato conseguimos forçar a inserção do registro com ID 5, então pela lógica o próximo que eu inserir sem parâmetro seguirá a sequência a partir do ID 6, correto? Incorreto, olhe só o que acontece quando inserimos um registro:

INSERT INTO  TAB_IDENTITY (DESCRITOR) VALUES ('CLIENTE6');

SELECT * FROM TAB_IDENTITY

Observe que nosso CLIENTE6 foi designado como ID 104, pois o contador do auto-increment prosseguiu a partir do maior index que havia sido inserido anteriormente.

Espero que tenha sido esclarecedor. Obrigado!

OBS*: Se alguém tiver uma solução para driblar essa limitação, por favor comente aqui nesse post!!!!

Olá Aurélio, tudo bem?

Desde já peço desculpas pela demora em obter retorno.

De fato, é uma limitação do recurso que ele não permita reiniciar o contador para um valor abaixo ou igual do último índice (ID) já inserido na tabela. Isso ocorre porque o auto-increment é projetado para evitar a inserção de registros duplicados e garantir a integridade referencial da tabela. Se o contador fosse reiniciado para um valor anterior, poderia haver conflitos de chave primária.

Uma possível solução para driblar essa limitação seria criar uma nova tabela com o auto-increment iniciando no valor desejado e, em seguida, fazer um INSERT SELECT para copiar os dados da tabela antiga para a nova tabela. Por exemplo:

CREATE TABLE nova_TAB_IDENTITY (ID INT AUTO_INCREMENT, DESCRITOR VARCHAR(20), PRIMARY KEY(ID)) AUTO_INCREMENT=5;

INSERT INTO nova_TAB_IDENTITY (DESCRITOR) SELECT DESCRITOR FROM TAB_IDENTITY;

Dessa forma, a nova tabela terá o auto-increment iniciando no valor 5 e todos os dados da tabela antiga serão copiados para ela.

Espero ter ajudado.

Aurélio, caso surja alguma dúvida, fico à dispósição.

Abraços e até mais!

Caso este post tenha lhe ajudado, por favor, marcar como solucionado ✓. Bons Estudos!

Chegou a hora de transformar