No se puede cambiar la columna utilizada en una restricción de clave foránea

Recibí este error cuando estaba tratando de alterar mi tabla. 

Error Code: 1833. Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'table.favorite_food'

Aquí está mi INSTRUCCIÓN CREATE TABLE Que se ejecutó con éxito.

CREATE TABLE favorite_food(
    person_id SMALLINT UNSIGNED,
    food VARCHAR(20),
    CONSTRAINT pk_favorite_food PRIMARY KEY(person_id,food),
    CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
    REFERENCES person (person_id)
);

Luego traté de ejecutar esta declaración y obtuve el error anterior.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
 68
mysql
Author: theJava, 2012-11-28
Source

3 answers

El tipo y la definición del campo de clave externa y la referencia deben ser iguales. Esto significa que su clave foránea no permite cambiar el tipo de su campo.

Una solución sería esta:

LOCK TABLES 
    favorite_food WRITE,
    person WRITE;

ALTER TABLE favorite_food
    DROP FOREIGN KEY fk_fav_food_person_id,
    MODIFY person_id SMALLINT UNSIGNED;

Ahora puedes cambiar tu person_id

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Recrear la clave externa 

ALTER TABLE favorite_food
    ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
          REFERENCES person (person_id);

UNLOCK TABLES;

EDITAR: Se han añadido bloqueos anteriores, gracias a los comentarios

Tienes que no permitir escribir en la base de datos mientras haces esto, de lo contrario, arriesga la integridad de los datos problema.

He añadido un bloqueo de escritura por encima

Todas las consultas de escritura en cualquier sesión que no sea la suya (INSERT, UPDATE, DELETE ) esperarán hasta el tiempo de espera o UNLOCK TABLES; se ejecuta

Http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html

EDIT 2: OP pidió una explicación más detallada de la línea "El tipo y la definición del campo de clave externa y la referencia deben ser iguales. Esto significa que su clave foránea no permite cambiar el tipo de su campo."

De Manual de referencia de MySQL 5.5: Restricciones de CLAVE EXTERNA

Las columnas correspondientes en la clave foránea y la clave referenciada deben tener tipos de datos internos similares dentro de InnoDB para que puedan ser comparado sin una conversión de tipo. El tamaño y el signo de los tipos enteros debe ser el mismo. La longitud de los tipos de cadena no tiene por qué ser la misma. Para columnas de cadena no binarias (carácter), el conjunto de caracteres y la intercalación debe ser el mismo.

 71
Author: Michel Feldheim,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-09 20:53:47

Puede desactivar las comprobaciones de claves foráneas: 

SET FOREIGN_KEY_CHECKS = 0;

/* DO WHAT YOU NEED HERE */

SET FOREIGN_KEY_CHECKS = 1;

Asegúrese de NO usar esto en producción y tener una copia de seguridad.

 118
Author: Dementic,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-07-05 20:28:42

Cuando establece claves (primarias o extranjeras), está estableciendo restricciones sobre cómo se pueden usar, lo que a su vez limita lo que puede hacer con ellas. Si realmente desea alterar la columna, podría volver a crear la tabla sin las restricciones, aunque lo recomendaría en contra. En términos generales, si tiene una situación en la que desea hacer algo, pero está bloqueada por una restricción, se resuelve mejor cambiando lo que desea hacer en lugar de la restricción.

 0
Author: RonaldBarzell,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-11-28 13:48:03