Django 2.0: sqlite IntegrityError: FOREIGN KEY constraint failed

Question

I'm working on adding Django 2.0 support to the django-pagetree library. During automated testing, using an sqlite in-memory database, I'm getting a bunch of errors like this:

  File "/home/nnyby/src/django-pagetree/pagetree/tests/test_models.py", line 638, in setUp
    'children': [],
  File "/home/nnyby/src/django-pagetree/pagetree/models.py", line 586, in add_child_section_from_dict

...

  File "/home/nnyby/src/django-pagetree/venv/lib/python3.5/site-packages/django/db/backends/base/base.py", line 239, in _commit
    return self.connection.commit()
django.db.utils.IntegrityError: FOREIGN KEY constraint failed

This is noted in the Django 2.0 release notes: https://docs.djangoproject.com/en/2.0/releases/2.0/#foreign-key-constraints-are-now-enabled-on-sqlite

From that description, which I don't fully understand, this shouldn't apply for test databases that aren't persistent, right? Wouldn't my sqlite test db get created with the appropriate options when using Django 2.0?

The app settings I'm using for testing are here: https://github.com/ccnmtl/django-pagetree/blob/master/runtests.py

Maybe you are missing the foreign key relation element. You should have created all the element which are mentioned as foreign key in model — Sadekur Rahaman
– Sadekur Rahaman, Commented Apr 30, 2022 at 18:19

dirkgroten · Accepted Answer · 2017-12-03 17:21:05Z

9

The documentation says two things:

If you have ForeignKey constraints they are now enforced at the database level. So make sure you're not violating a foreign key constraint. That's the most likely cause for your issue, although that would mean you'd have seen these issues with other databases. Look for patterns like this in your code:
```
# in pagetree/models.py, line 810
@classmethod
def create_from_dict(cls, d):
    return cls.objects.create()  # what happens to d by the way?
```
This will definitely fail with a ForeignKey constraint error since a PageBlock must have section, so you can't call create without first assigning it.
If you circumvent the foreign key constraint by performing an atomic transaction (for example) to defer committing the foreign key, your Foreign Key needs to be INITIALLY DEFERRED. Indeed, your test db should already have that since it's rebuilt every time.

answered Dec 3, 2017 at 17:21

dirkgroten

20.8k2 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nnyby Over a year ago

Thanks for digging in, and I think you found a bug here - d should be passed in to cls.objects.create().

Eric Jin · Accepted Answer · 2022-04-30 18:56:58Z

9

I met a different situation with the same error. The problem was that I used the same Model name and field name

Incorrect code:

class Column(models.Model):
    ...

class ColumnToDepartment(models.Model):
    column = models.ForeignKey(Column, on_delete=models.CASCADE)

Solution:

class Column(models.Model):
    ...

class ColumnToDepartment(models.Model):
    referring_column = models.ForeignKey(Column, on_delete=models.CASCADE)

edited Apr 30, 2022 at 18:56

Eric Jin

3,9244 gold badges23 silver badges48 bronze badges

answered May 29, 2019 at 13:59

Yurii Hierts

1,9701 gold badge23 silver badges35 bronze badges

1 Comment

Tony Park Over a year ago

Is there an explanation for this? Python is case sensitive... but I guess SQLite is not and that is the gotcha here?

Krzysieqq · Accepted Answer · 2017-12-03 16:55:51Z

2

Do you have add on_delete to your FOREIGN KEY? On Django 2.0 this argument is required. You could see also: https://docs.djangoproject.com/en/2.0/ref/models/fields/#django.db.models.ForeignKey.on_delete https://docs.djangoproject.com/en/2.0/howto/upgrade-version/ https://docs.djangoproject.com/en/2.0/topics/db/examples/many_to_one/ https://docs.djangoproject.com/en/2.0/ref/models/fields/#django.db.models.ForeignKey

answered Dec 3, 2017 at 16:55

Krzysieqq

1,21110 silver badges11 bronze badges

2 Comments

nnyby Over a year ago

Yes I have, to my model file and migrations, so I don't think that's the issue. If you're interested, my current django 2.0 diff is here: github.com/ccnmtl/django-pagetree/pull/187/files

Kim Palao Over a year ago

This one actually helped me. I had my on_delete attributes set to DO_NOTHING, which ended up violating foreign key constraints. Setting it to SET_NULL fixed it for me.

Montaro · Accepted Answer · 2022-08-07 16:13:22Z

2

In my case, I found that the ForeignKey object that my model refers to does not exist. Therefore, I change the referenced FK object to the existing object.

edited Aug 7, 2022 at 16:13

Montaro

10k6 gold badges32 silver badges30 bronze badges

answered Jun 18, 2020 at 2:33

Lê Hoàng Vũ

1431 silver badge2 bronze badges

Comments

Carlo · Accepted Answer · 2022-05-05 11:30:18Z

1

A reason could be you miscalculated what happens when you delete an item from the db that is linked with a foreign key to something else, elsewhere.

p.e. what happens when you delete an author that has active books?

I don't meant to enter into the logic of the application, but consider for example to cascade the deletion of the elements linked to that key.

Here is an example, in this case we are dealing with "post" attribute for each "user"

user = models.ForeignKey(User, related_name='posts', on_delete=models.CASCADE)

Here's a careful explanation, Django 4.0: https://docs.djangoproject.com/en/4.0/ref/models/fields/

answered May 5, 2022 at 11:30

Carlo

5545 silver badges15 bronze badges

Comments

Bennie van Eeden · Accepted Answer · 2019-07-22 20:49:58Z

0

I just had this error: sqlite3.IntegrityError: FOREIGN KEY constraint failed on my Django project. Turns out I deleted the migrations folder somewhere along the line so it didn't pick up my model changes when I ran python manage.py makemigrations. Just make sure you still have a migrations folder with migrations in.

answered Jul 22, 2019 at 20:49

Bennie van Eeden

4484 silver badges5 bronze badges

Comments

Theo Godard · Accepted Answer · 2020-01-01 07:24:15Z

0

One more thing to check, in my case it was related to my fixtures files. Regenerating them after migration to Django3 solved the issue I had while testing my app.

./manage.py dumpdata app.Model1 app.Model2 --indent=4 > ./app/fixtures/file.json

answered Jan 1, 2020 at 7:24

Theo Godard

3681 silver badge7 bronze badges

Comments

Michael · Accepted Answer · 2020-07-22 02:40:41Z

0

When I have trouble with migrations or tables, I do it and it very often helps:

Comment your trouble strings;
Do python3 manage.py makemigrations and python3 manage.py migrate;
Then u must do python3 manage.py migrate --fake;
Uncomment your strings and do it again python3 manage.py makemigrations and python3 manage.py migrate.

I hope it is useful for u

edited Jul 22, 2020 at 2:40

Michael

7678 silver badges34 bronze badges

answered Apr 9, 2020 at 11:32

Block2busted

471 silver badge7 bronze badges

Comments

Mostafa Ghadimi · Accepted Answer · 2021-01-20 00:07:43Z

My problem was solved after doing the migration, because I have altered the foreign key and then didn't apply the migrations.

Specifically, at first I have the following piece of code in models:

class TeacherRequest(models.Model):
    requester = models.ForeignKey(
        Profile,
        on_delete=models.CASCADE,
        related_name="teacher_request",
    )

    class RequestStatus(models.TextChoices):
        PENDING = '1', _('pending')
        APPROVED = '2', _('approved')
        REJECTED = '3', _('rejected')

    status = models.CharField(
        choices=RequestStatus.choices,
        max_length=1,
        default=RequestStatus.PENDING,
    )

Then I have changed the foreign key from Profile to User:


class TeacherRequest(models.Model):
    requester = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="teacher_request",
    )

    class RequestStatus(models.TextChoices):
        PENDING = '1', _('pending')
        APPROVED = '2', _('approved')
        REJECTED = '3', _('rejected')

    status = models.CharField(
        choices=RequestStatus.choices,
        max_length=1,
        default=RequestStatus.PENDING,
    )

Solution

python manage.py makemigrations
python manage.py migrate

abhishek khot · Accepted Answer · 2022-06-24 18:16:48Z

0

Most probably the cause of the error is the lacking of matching Foreignkey elements in your model and the one your trying to save(update using that foreignkey). Make sure that the foreignkey constraints(value) are present in both your and the new data

answered Jun 24, 2022 at 18:16

abhishek khot

11 bronze badge

Comments

oruchkin · Accepted Answer · 2022-09-07 07:26:49Z

0

I met a different situation with the same error.

The problem was that i had my on_delete attributes set to DO_NOTHING, which ended up violating foreign key constraints.

Setting it to SET_NULL fixed it for me.

answered Sep 7, 2022 at 7:26

oruchkin

1,3031 gold badge16 silver badges26 bronze badges

Collectives™ on Stack Overflow

Django 2.0: sqlite IntegrityError: FOREIGN KEY constraint failed

11 Answers 11

1 Comment

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

1 Comment

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related