CODEWITHSUNDEEP
djangodjango-modelsdjango-migrations
Sam
Sam

Reputation: 796

Django migration IntegrityError: invalid foreign key (but the data exists)

I am gradually updating a legacy Django application from 1.19 -> 2.2 and beyond. To upgrade to 2.2, I just added on_delete=models.CASCADE to all models.ForeignKey fields that did not have the error (which I also had to do retroactively for existing migrations, apparently...).

Possibly related/unrelated to that, when I run manage.py migrate, Django throws the following error (I shortened the table/field names for brevity):

django.db.utils.IntegrityError: The row in table 'X' with primary key '3' has an invalid foreign key: X.fieldname_id contains a value '4' that does not have a corresponding value in Y__old.id.

Note in particular the __old.id suffix for the db table that Django expects to contain a row with id 4. When manually inspecting the db, the table Y does really contain a valid row with id 4! I'm assuming, to support the migration, Django is making some temporary tables suffixed with __old and somehow it is unable to migrate said data?

The db row Y in question is really simple: a char, boolean, and number column.

Edit: seems to be related to an old Django bug with SQLite. Not sure how to solve. It does not seem to occur for Django 2.1.15, and starts to occur in Django 2.2.

Upvotes: 3

Views: 1116

Answers (2)

chrisv
chrisv

Reputation: 1481

This problem is caused by the mentioned Django bug, but if you get the migration error, your database is broken already.

When you dump the DB to SQL, you can see REFERENCES statements which point to tables ending in __old, but these tables do not actually exist:

$> sqlite3 mydb.db .dump | grep '__old'

CREATE TABLE IF NOT EXISTS "company" [...]"account_id" integer NULL REFERENCES "account__old" ("id") [...]

Fortunately, the DB can be fixed easily, by just removing the __old and dumping into a new database. This can be automated with sed:

sqlite3 broken.db .dump | sed 's/REFERENCES "\(.[^"]*\)__old"/REFERENCES "\1"/g' | sqlite3 fixed.db

Upvotes: 2

Alain Bianchini
Alain Bianchini

Reputation: 4191

It is not an ideal solution, but you can manually delete the row from the database or set the value of the foreign key to a temporary value, migrate and then restore the original value.

Upvotes: 0

Related Questions