Cannot change column used in a foreign key constraint
The type and definition of foreign key field and reference must be equal. This means your foreign key disallows changing the type of your field.
One solution would be this:
LOCK TABLES
favorite_food WRITE,
person WRITE;
ALTER TABLE favorite_food
DROP FOREIGN KEY fk_fav_food_person_id,
MODIFY person_id SMALLINT UNSIGNED;
Now you can change you person_id
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
recreate foreign key
ALTER TABLE favorite_food
ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id);
UNLOCK TABLES;
EDIT: Added locks above, thanks to comments
You have to disallow writing to the database while you do this, otherwise you risk data integrity problems.
I've added a write lock above
All writing queries in any other session than your own ( INSERT, UPDATE, DELETE
) will wait till timeout or UNLOCK TABLES
; is executed
http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
EDIT 2: OP asked for a more detailed explanation of the line "The type and definition of foreign key field and reference must be equal. This means your foreign key disallows changing the type of your field."
From MySQL 5.5 Reference Manual: FOREIGN KEY Constraints
Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.
You can turn off foreign key checks:
SET FOREIGN_KEY_CHECKS = 0;
/* DO WHAT YOU NEED HERE */
SET FOREIGN_KEY_CHECKS = 1;
Please make sure to NOT use this on production and have a backup.
In my case it was necessary to add 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;