Nie można zmienić kolumny używanej w ograniczeniu klucza obcego

Wystąpił ten błąd, gdy próbowałem zmienić mój stół.

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

Oto moje STWÓRZ STANOWISKO TABELI, które zostały pomyślnie uruchomione.

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

Następnie próbowałem wykonać tę instrukcję i otrzymałem powyższy błąd.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Typ i definicja pola klucza obcego i odwołania muszą być równe. Oznacza to, że klucz obcy nie pozwala na zmianę typu pola.

Jednym rozwiązaniem byłoby to:

LOCK TABLES 
    favorite_food WRITE,
    person WRITE;

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

Teraz możesz zmienić swój identyfikator person_id

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

odtworzyć klucz obcy

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

UNLOCK TABLES;

EDYCJA: Dodano blokady powyżej, dzięki komentarzom

Musisz zabronić zapisywania do bazy danych podczas tej czynności, w przeciwnym razie ryzykujesz problemy z integralnością danych.

Dodałem powyżej blokadę zapisu

Wszystkie zapytania zapisujące w dowolnej sesji innej niż Twoja ( INSERT, UPDATE, DELETE) będą czekać do przekroczenia limitu czasu lub UNLOCK TABLES; jest wykonywany

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

EDYCJA 2: OP poprosił o bardziej szczegółowe wyjaśnienie wiersza „Typ i definicja pola klucza obcego oraz odwołanie muszą być równe. Oznacza to, że klucz obcy nie pozwala na zmianę typu pola”.

Z Podręcznika MySQL 5.5: Ograniczenia klucza obcego

Odpowiednie kolumny w kluczu obcym i przywoływanym kluczu muszą mieć podobne wewnętrzne typy danych w InnoDB, aby można je było porównać bez konwersji typu. Rozmiar i znak typów całkowitych muszą być takie same. Długość typów ciągów nie musi być taka sama. W przypadku kolumn z ciągami niebinarnymi (znakowymi) zestaw znaków i sortowanie muszą być takie same.

Możesz wyłączyć sprawdzanie kluczy obcych:

SET FOREIGN_KEY_CHECKS = 0;

/* DO WHAT YOU NEED HERE */

SET FOREIGN_KEY_CHECKS = 1;

Upewnij się, że NIE używasz tego na produkcji i masz kopię zapasową.

Kiedy ustawiasz klucze (podstawowe lub obce), ustawiasz ograniczenia dotyczące tego, jak mogą być używane, co z kolei ogranicza to, co możesz z nimi zrobić. Jeśli naprawdę chcesz zmienić kolumnę, możesz ponownie utworzyć tabelę bez ograniczeń, chociaż odradzam to. Ogólnie rzecz biorąc, jeśli masz sytuację, w której chcesz coś zrobić, ale jest to blokowane przez ograniczenie, najlepiej rozwiązać to, zmieniając to, co chcesz zrobić, zamiast ograniczenia.