Cannot change column used in a foreign key constraint

Question

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

User · Answer

When you set keys  primary or foreign  you are setting constraints on how they can be used  which in turn limits what you can do with them   If you really want to alter the column  you could re-create the table without the constraints  although I d recommend against it   Generally speaking  if you have a situation in which you want to do something  but it is blocked by a constraint  it s best resolved by changing what you want to do rather than the constraint

User · Answer

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

User · Answer

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

[mysql] Cannot change column used in a foreign key constraint

Examples related to mysql