Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot change column used in a foreign key constraint

Tags:

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; 
like image 318
theJava Avatar asked Nov 28 '12 13:11

theJava


People also ask

Can we change the value of foreign key column?

2 Answers. Show activity on this post. You can change the value in Table2, so long as the value you are changing it to is already contained in Table1. If you want to change it to a value which is not already contained in Table1, you can add the value to Table1 first, and then change the value in Table2 to that value.

How do I fix foreign key constraint failure?

The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.

Can any column be a foreign key?

General standard answer is no. It is only possible if foreign key refers any column uniquely in other table. That means foreign key must be the candidate key in other table and primary key is also a candidate key the table.


2 Answers

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.

like image 82
Rafael Herscovici Avatar answered Oct 19 '22 05:10

Rafael Herscovici


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.

like image 38
Michel Feldheim Avatar answered Oct 19 '22 03:10

Michel Feldheim