How to update case of field with FK constraint to it

Question

I am looking to standardize fields in my database. How would I do the following, where the field has a FK constraint to it:

In django:

Platform.objects.filter(name='ITUNES').update(name='iTunes')
# gives FK error

In mysql:

update main_platform set name='iTunes' where name='ITUNES'
# Cannot delete or update a parent row: a foreign key constraint fails (`avails`.`main_credit`, CONSTRAINT `main_credit_ibfk_3` FOREIGN KEY (`platform_id`) REFERENCES `main_platform` (`name`))

What would be the solution for this?

Please note that I'm not looking to add additional fields, such as an ID field, where the FK will never change, I'm only interested in updating the existing field.

The current table I have for the Platform is:

CREATE TABLE `main_platform` (
  `name` varchar(20) NOT NULL,
  `guid` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You're going to need to give more information. What type is this field? What is the constraint? — Daniel Roseman, Aug 07 '15 at 18:06

score 1 · Accepted Answer · edited May 23 '17 at 11:51

This looks to be more of an SQL problem than anything.

You have name as a primary key, so if you try to update it directly when there are rows in linked tables referencing it, then things are definitely going to break.

If you're not going to modify the table to use a numeric id as the primary key (which would be a really good idea), then the best real option you have that I can see is to add a new row to main_platform with the new name field (iTunes vs ITUNES), then migrate any related data to point to that new row, and finally delete the old row.

See this answer to get some more insight into the problem, and hopefully to be persuaded to and and use a numeric id as your primary key.

score 1 · Answer 2 · edited May 23 '17 at 12:29

Be sure to shut down your application first, as this setting is system-wide.

from django.db import connection

assert connection.vendor == 'mysql'
cursor = connection.cursor()
cursor.execute("SET FOREIGN_KEY_CHECKS = 0")

try:
    # do your data migration here
finally:
    cursor.execute("SET FOREIGN_KEY_CHECKS = 1")

Warning from the docs:

Rows added to the table while foreign_key_checks = 0 will not be verified for consistency

You might want to check out this question as well.

Still, you really should use numeric ids.

How to update case of field with FK constraint to it

2 Answers2