Невозможно изменить столбец, используемый для ограничения внешнего ключа

Я получил эту ошибку, когда пытался изменить таблицу.

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

Вот мой CREATE TABLE STATEMENT, который успешно завершился.

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

Затем я попытался выполнить этот оператор, и я получил указанную выше ошибку.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Ответы

Ответ 1

Тип и определение поля внешнего ключа и ссылки должны быть равны. Это означает, что ваш внешний ключ запрещает изменять тип вашего поля.

Одним из решений было бы следующее:

LOCK TABLES 
    favorite_food WRITE,
    person WRITE;

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

Теперь вы можете изменить you person_id

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

воссоздать внешний ключ

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

UNLOCK TABLES;

EDIT: Добавлены блокировки выше, благодаря комментариям

Вы должны запретить запись в базу данных, пока вы это делаете, в противном случае вы рискуете проблемы с целостностью данных.

Я добавил блокировку записи выше

Все записи запросов в любом другом сеансе, кроме вашего собственного (INSERT, UPDATE, DELETE), будут ждать до таймаута или UNLOCK TABLES; выполняется

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

EDIT 2: OP запросил более подробное объяснение строки "Тип и определение поля внешнего ключа и ссылки должны быть равными. Это означает, что ваш внешний ключ запрещает изменять тип вашего поля".

Из Справочное руководство MySQL 5.5: ограничения FOREIGN KEY

Соответствующие столбцы внешнего ключа и ссылочного ключа должны имеют аналогичные внутренние типы данных внутри InnoDB, так что они могут быть сравниваются без преобразования типа. Размер и знак целочисленных типов должны быть одинаковыми. Длина типов строк не обязательно должна быть одинаковой. Для небинные (символьные) строковые столбцы, набор символов и сортировка должны быть одинаковыми.

Ответ 2

Вы можете отключить проверку внешнего ключа:

SET FOREIGN_KEY_CHECKS = 0;

/* DO WHAT YOU NEED HERE */

SET FOREIGN_KEY_CHECKS = 1;

Пожалуйста, не забудьте использовать это при производстве и иметь резервную копию.

Ответ 3

Когда вы устанавливаете ключи (первичные или внешние), вы устанавливаете ограничения на то, как их можно использовать, что в свою очередь ограничивает то, что вы можете с ними делать. Если вы действительно хотите изменить столбец, вы можете воссоздать таблицу без ограничений, хотя я бы рекомендовал против нее. Вообще говоря, если у вас есть ситуация, в которой вы хотите что-то сделать, но она блокируется ограничением, ее лучше решить, изменив то, что вы хотите сделать, а не ограничение.