Impossible de changer la colonne utilisée dans une contrainte de clé étrangère

Question

J'ai eu cette erreur lorsque j'ai essayé de modifier ma table.

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

Voici mon CREATE TABLE STATEMENT qui s'est exécuté avec succès.

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

J'ai ensuite essayé d'exécuter cette déclaration et j'ai obtenu l'erreur ci-dessus.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Answer 1

Vous pouvez désactiver les vérifications des clés étrangères :

SET FOREIGN_KEY_CHECKS = 0;

/* DO WHAT YOU NEED HERE */

SET FOREIGN_KEY_CHECKS = 1;

Veillez à ne PAS l'utiliser en production et à avoir une sauvegarde.

Answer 2

Le type et la définition du champ et de la référence de la clé étrangère doivent être égaux. Cela signifie que votre clé étrangère ne permet pas de modifier le type de votre champ.

Une solution serait la suivante :

LOCK TABLES 
    favorite_food WRITE,
    person WRITE;

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

Maintenant vous pouvez changer votre person_id

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

recréer une clé étrangère

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

UNLOCK TABLES;

EDIT : Ajouté les serrures ci-dessus, grâce aux commentaires

Vous devez interdire l'écriture dans la base de données pendant que vous faites cela, sinon vous risquez des problèmes d'intégrité des données.

J'ai ajouté un verrou d'écriture ci-dessus

Toutes les requêtes d'écriture dans une autre session que la vôtre ( INSERT, UPDATE, DELETE ) attendra jusqu'à ce que le délai d'attente ou UNLOCK TABLES ; est exécuté

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

EDIT 2 : OP a demandé une explication plus détaillée de la ligne "Le type et la définition du champ de la clé étrangère et de la référence doivent être égaux. Cela signifie que votre clé étrangère interdit de modifier le type de votre champ."

Desde Manuel de référence de MySQL 5.5 : Contraintes FOREIGN KEY

Les colonnes correspondantes dans la clé étrangère et la clé référencée doivent avoir des types de données internes similaires dans InnoDB afin qu'elles puissent être comparées sans conversion de type. La taille et le signe des types entiers doivent être les mêmes. La longueur des types de chaînes de caractères ne doit pas être la même. Pour colonnes de chaînes de caractères non binaires, le jeu de caractères et la collation doivent être les mêmes.

Answer 3

Dans mon cas, il a fallu ajouter GLOBAL .

SET FOREIGN_KEY_CHECKS = 0;
SET GLOBAL FOREIGN_KEY_CHECKS=0;

/* DO WHAT YOU NEED HERE */

SET FOREIGN_KEY_CHECKS = 1;
SET GLOBAL FOREIGN_KEY_CHECKS=1;

Answer 4

Allez dans l'onglet structure de la table en question. Sous actions, vous avez des index. Déposez-les

Une fois que vous avez terminé les modifications nécessaires, Rapportez la clé étrangère et restaurez les index supprimés. Vérifiez ensuite que votre structure est la même et n'a pas changé.

Answer 5

Lorsque vous définissez des clés (primaires ou étrangères), vous définissez des contraintes sur la façon dont elles peuvent être utilisées, ce qui limite à son tour ce que vous pouvez faire avec elles. Si vous voulez vraiment modifier la colonne, vous pouvez recréer la table sans les contraintes, mais je vous le déconseille. D'une manière générale, si vous voulez faire quelque chose, mais que cette action est bloquée par une contrainte, la meilleure solution consiste à modifier ce que vous voulez faire plutôt que la contrainte.