How to change primary key that references foreign key for auto increment

Question

How to change primary key that references foreign key for auto increment

Navigation

#1 by (4 votes)

3

I have two tables, one call aluno and another call HistoricoAluno . After creating them, a change was made 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 did not succeed. How can I leave the primary key of the alunos table as auto_increment ?

I'm using mySQL in phpMyAdmin.

mysql

asked by anonymous 20.11.2015 / 20:30

1 answer

ArrayList for String with split [closed] Problem removing element from a linked list

score 4 · Accepted Answer

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

-- 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 FK columns must have the same configuration (type, size, signature, etc ...).