readthefuckingmanual.net

[SOLVED] Cannot change column used in a foreign key constraint of table

Error added: 2013-07-30T12:07:44Z

0 people waiting for the answer...

1 answers found.

Answer 2165 (0.0% helpful)

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Before MySQL 5.6.7, using ALTER TABLE to change the definition of a foreign key column could cause a loss of referential integrity. For example, changing a foreign key column that contained NULL values to be NOT NULL caused the NULL values to be the empty string. Similarly, an ALTER TABLE IGNORE that removed rows in a parent table could break referential integrity.

As of 5.6.7, the server prohibits changes to foreign key columns with the potential to cause loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward. 
Permalink

Add an answer/solution

If you know the answer, please add your own solution below.
If you don't know, but find out later, please come back and share your answer - there will be other people struggling with this too.


Please enter 61948 here

If you want to be notified via email when this is solved, enter your email address here: