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;

Stamin

The above example is from the book "Learning SQL, 2nd edition". I hope the author, Alan Beaulieu makes corrections. – Rinderpest

Answer 1

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.

Bluebottle

Don't forget to use a transaction for this. Otherwise your database might get corrupted. – Depopulate

Good point, unfortunately MySQL does not support transactions around DDL statements. Open transactions are commited before a DDL query is executed see dev.mysql.com/doc/refman/5.5/en/implicit-commit.html – Bluebottle

@FrancoisBourgeois: Can you explain me more on this line alone: The type and definition of foreign key field and reference must be equal – Stamin

The correct statement to recreate a foreign key would be: ALTER TALE favorite_food ADD CONTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person(id); – Mcniel

Why do you modify person_id right after dropping the foreign key? Looks like you didn't change anything since it's already a SMALLINT UNSIGNED. – Renfrew

We don’t know what it was since he only posted the referencing table structure. Innodb has int as internal type, smallint etc are shortcuts only – Bluebottle

This explanation makes no sense. The OP was not changing the type. They were only affecting the default value for of the person_id for newly inserted persons. If they were trying to turn the person_id of the favorite_food table into an auto increment, then it would make sense, but that is not what they were doing. – Osric

Answer 2

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.

Callimachus

Seems to be very insecure solution. Can it potentially lead to loss of data integrity? – Pile

@Synaps - yes it could if you are deleting/updating/inserting. data loss will not occur if you are just modifying a table or seeding your db, on the other hand you should validate manually your data (since you remove the constraints) – Callimachus

This solution is great, and you can use it on production if you ad write locks before modifying your data and then an unlock when you're done. Using an sql file to do your changes in the shortest amount of time possible would be even better. – Gabe

Good solution for fast tweaks – Publicity

You don't need a lock as SET FOREIGN_KEY_CHECKS is session-scoped (other sessions will still have FK constraint applied). It's perfect for adding/removing AUTO_INCREMENT (which does not change actual column datatype), but it won't work if you try to change the column datatype for "real" (say, from SMALLINT to INT) as you'll get a legitimate 150 FK constraint incorrectly formed when mysql tries to replace the old table by the new one. In such case, use the accepted answer. – Pavement

That works locally, so thank you. Then what is the solution for production? This is not a complete answer – Facet

Answer 3

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;

Elfont

Why we should impact GLOBAL ? – Goldeye

Answer 4

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

Modulation

Answer 5

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.

Conan

Answer 6

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.

Autostability

@ajmedway Then you can write a helpful answer without blaming the other users – Kremenchug

@IamtheMostStupidPerson It's much better than just downvoting without a comment. At least the commenter can guess why the downvotes. Such generic answers like "better be safe than sorry" are not useful. – Dichroic