How to change primary key referencing foreign key to auto increment

Question

I have two tables, one call aluno and another call HistoricoAluno. After creating them, a change was missing which was to leave the primary key of the table alunos as auto_increment. By trying to make that change I always get the following error:

    Query error:
#1833 - Cannot change column 'idAluno': used in a foreign key constraint 'historicoaluno_ibfk_1' of table 'matriculas.historicoaluno

So I tried some solutions, like this:

SET FOREIGN_KEY_CHECKS = 0;

/* realizar as alterações */

SET FOREIGN_KEY_CHECKS = 1;

But I did not succeed. How can I leave the table primary key alunos as auto_increment?

I am using mysql in phpMyAdmin.

Answer 1

You need to remove the Fk reference before changing the column in the other table, try this way:

-- Desabilita as verificações de FKs
SET FOREIGN_KEY_CHECKS = 0;

-- Deleta a referência de FK da tabela HistoricoAluno
ALTER TABLE HistoricoAluno DROP FOREIGN KEY historicoaluno_ibfk_1;

-- Altera a coluna idAluno para AUTO_INCREMENT na tabela Aluno
ALTER TABLE Aluno MODIFY COLUMN idAluno INT AUTO_INCREMENT;

-- Cria a FK novamente
ALTER TABLE HistoricoAluno ADD CONSTRAINT historicoaluno_ibfk_1 FOREIGN KEY (idAluno) REFERENCES Aluno(idAluno);

-- Habilita as verificações de FKs
SET FOREIGN_KEY_CHECKS = 1;

Obs.: It is important to note that FK columns must have the same configuration (type, size, signature, etc...).