mysql 修改外键约束列_无法更改外键约束中使用的列

I got this error when i was trying to alter my 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 Which ran 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;

解决方案

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

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."

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.