mysql – How to change primary key referencing foreign key to auto increment

Question:

I have two tables, one called aluno and another called HistoricoAluno . After creating them, there was a change that was to leave the primary key of the alunos table as auto_increment . When trying to make this 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 was not successful. How can I leave the primary key of the alunos table as auto_increment ?

I'm using mySQL in phpMyAdmin.

Answer:

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

-- 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;

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

Post Views: 11