CODEWITHSUNDEEP
pythondjangodjango-modelssqliteforeign-keys
sadie parker
sadie parker

Reputation: 381

Unable to delete items from Django sqlite3 database, with error "foreign key constraint failed"

There are some items that are not deletable or editable from my Django sqlite3 database. When trying to delete these items, I get integrity error: foreign key constraint failed.

The frustrating thing is that this isn't the case with the majority of items in my database. I believe it is only affecting a few rows, which I must have created before making some tweaks to the models. I don't recall what exact changes I made to the models.py file, but I don't think it was anything drastic. I don't have information about the migrations I made, because I deleted these files thinking that it might resolve the problem.

I have included an example of how the classes in models.py are currently set up. Nothing complicated, standard models with a simple foreign key, which isn't even a required field.

I have tried editing my migrations files, completely removing my migration history, editing the models via models.py.

models.py:

class ExampleOne(models.Models):
    title = model.CharField(max_length=500, null=True, blank=True)

class ExampleTwo(models.Models):
    title = model.CharField(max_length=500, null=True, blank=True)
    example_one = models.ForeignKey(ExampleOne, null=True, blank=True, on_delete=models.CASCADE)

I'd rather not delete my database file. I'd to be able to delete these instances of exampleone and exampletwo, but when I try to delete any item created before I had tweaked my model files, I get the error:

django.db.utils.IntegrityError: FOREIGN KEY constraint failed

Upvotes: 4

Views: 3843

Answers (2)

sadie parker
sadie parker

Reputation: 381

I solved this issue by first making a backup of my database (in case I messed something up) and then connecting to the database on the command line like so:

$ python
> import sqlite3
> conn = sqlite3.connect(name_of_db_file.sqlite3)
> cur = conn.cursor()
> cur.execute("DELETE FROM appname_exampletwo WHERE appname_exampleone_id='[id of undeletable item]'")
> conn.commit()
> conn.close()

This particular database query requires knowing which items are not deletable / editable, and requires that you go through each one, but it does the trick.

Afterwards, I did get some errors when migrating the project, relating to what I'm guessing is a relational table that was left hanging when I deleted these items.

django.db.utils.IntegrityError: The row in table 'appname_exampleone_exampletwos' with primary key '[some_int]' has an invalid foreign key: appname_exampleone_exampletwos.exampletwo_id contains a value '[some_other_int}' that does not have a corresponding value in appname_exampletwo.id.

This made me a bit nervous, but I went through and deleted the problem rows in this relational table:

> cur.execute("SELECT * FROM appname_exampleone_exampletwos WHERE id='[some_int]'")

Ultimately everything looks good.

Upvotes: 1

Ben Boyer
Ben Boyer

Reputation: 1234

This is because you are missing the on_delete in ForeignKey, it's mandatory in new Django Version.

Add the following on_delete acording to your app and makemigrations and migrate again. Then it should be fine.

From the doc: https://docs.djangoproject.com/en/2.2/ref/models/fields/#arguments

ForeignKey accepts other arguments that define the details of how the relation works.

ForeignKey.on_delete

When an object referenced by a ForeignKey is deleted, Django will emulate the behavior of the SQL constraint specified by the on_delete argument. For example, if you have a nullable ForeignKey and you want it to be set null when the referenced object is deleted:

user = models.ForeignKey(
    User,
    models.SET_NULL,
    blank=True,
    null=True,
)

on_delete doesn’t create a SQL constraint in the database. Support for database-level cascade options may be implemented later.

The possible values for on_delete are found in django.db.models:

CASCADE

Cascade deletes. Django emulates the behavior of the SQL constraint ON DELETE CASCADE and also deletes the object containing the ForeignKey.

Model.delete() isn’t called on related models, but the pre_delete and post_delete signals are sent for all deleted objects.

PROTECT

Prevent deletion of the referenced object by raising ProtectedError, a subclass of django.db.IntegrityError.

SET_NULL

Set the ForeignKey null; this is only possible if null is True.

SET_DEFAULT Set the ForeignKey to its default value; a default for the ForeignKey must be set.

SET()

Set the ForeignKey to the value passed to SET(), or if a callable is passed in, the result of calling it. In most cases, passing a callable will be necessary to avoid executing queries at the time your models.py is imported:

    from django.conf import settings
    from django.contrib.auth import get_user_model
    from django.db import models

    def get_sentinel_user():
        return get_user_model().objects.get_or_create(username='deleted')[0]

    class MyModel(models.Model):
        user = models.ForeignKey(
            settings.AUTH_USER_MODEL,
            on_delete=models.SET(get_sentinel_user),
        )

DO_NOTHING

Take no action. If your database backend enforces referential integrity, this will cause an IntegrityError unless you manually add an SQL ON DELETE constraint to the database field.

Upvotes: 3

Related Questions