CODEWITHSUNDEEP
mysql
theJava
theJava

Reputation: 15034

Cannot change column used in a foreign key constraint

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;

Upvotes: 173

Views: 195741

Answers (6)

BurninLeo
BurninLeo

Reputation: 4464

SET FOREIGN_KEY_CHECKS = 0; may not solve the ALTER TABLE issue in some instances. I was not able to find out, why it did not work on one machine, while working well on another.

One possible solution then is to remove the foreign key (ALTER TABLE DROP CONTRAINT ...), do the changes, and restore the contraint after the update (ALTER TABLE ADD CONTRAINT ...). Make sure to note down the creation code (for example using HeidiSQL) before removing the contraint ;)

Important: Additional measures such als locking the tables may be necessary to ensure consistency during such an operation.

Upvotes: 1

kurtko
kurtko

Reputation: 2126

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;

Upvotes: 9

Bekh2505
Bekh2505

Reputation: 1

Go to the structure tab of the table in question. Under actions you have indexes. Drop them

Once you are finished with the necessary modifications, Bring back the foreign key and restore the indexes deleted. Then make sure your structure is the same and has not changed

Upvotes: 0

Michel Feldheim
Michel Feldheim

Reputation: 18250

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.

Upvotes: 167

Rafael Herscovici
Rafael Herscovici

Reputation: 17094

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.

Upvotes: 284

RonaldBarzell
RonaldBarzell

Reputation: 3830

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.

Upvotes: -4

Related Questions