Cannot change column used to restrict foreign key

Question

I got this error when I tried to modify the 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' 

Here is my CREATE TABLE STATEMENT that completed successfully.

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

Then I tried to execute this statement and I got the above error.

 ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; 

Answer 1

The type and definition of the foreign key field and the link must be equal. This means that your foreign key forbids changing the type of your field.

One solution would be the following:

 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 must prohibit writing to the database while you do this, otherwise you risk data integrity problems.

I added write lock above

All query records in any other session except your own ( INSERT, UPDATE, DELETE ) will wait until a timeout or UNLOCK TABLES ; performed

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

EDIT 2: OP requested a more detailed explanation of the line "The type and definition of the foreign key field and the link must be equal. This means that your foreign key does not allow you to change the type of your field."

From MySQL 5.5 Reference Guide: FOREIGN KEY Limitations

The corresponding foreign key and reference key columns must have similar internal data types inside InnoDB, so that they can be compared without type conversion. The size and sign of integer types must be the same. String types do not have to be the same length. For non-non-character (character) string columns, the character set and sorting must be the same.

Answer 2

You can disable foreign key verification:

 SET FOREIGN_KEY_CHECKS = 0; /* DO WHAT YOU NEED HERE */ SET FOREIGN_KEY_CHECKS = 1; 

Please remember to use this in production and have a backup.

Answer 3

When you install keys (primary or foreign), you set limits on how you can use them, which in turn limits what you can do with them. If you really want to change the column, you can recreate the table without restrictions, although I would recommend against it. Generally speaking, if you have a situation in which you want to do something, but it is blocked by a restriction, it is better to solve it by changing what you want to do, not the restriction.