Opened 6 years ago

Last modified 6 years ago

#29928 closed Bug

TestCase doesn't check for foreign key constraints when using sqlite — at Initial Version

Reported by: Michel Samia Owned by: nobody
Component: Testing framework Version: dev
Severity: Normal Keywords: sqlite db foreign key TestCase
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Hi,

When I create some stupid insertion, foreign keys are not checked in test when using default sqlite engine. It looks like a regression to https://code.djangoproject.com/ticket/11665
sqlite3 driver doesn't enforce foreign key constraints until commit is called - impossible to use them in TestCase

In the first test method (low level) I ensure that sqlite is able to catch such foreign key violations. In the second (high level) I prove that django effectively disables this check in TestCase.

models.py:

class Person(models.Model):
    name = models.CharField(max_length=20)
    mom = models.ForeignKey('Person', on_delete=models.CASCADE, null=True)

tests.py:

import sqlite3

from django.test import TestCase
from .models import Person


# Create your tests here.
class AppTests(TestCase):
    def test_sqlite_constraints_low_level(self):
        conn = sqlite3.connect(':memory:')
        c = conn.cursor()

        # Create table
        c.execute('''CREATE TABLE contacts (
         id INTEGER PRIMARY KEY,
         name TEXT NOT NULL,
         mom INTEGER,
         FOREIGN KEY(mom) REFERENCES contacts(id)
        )
        ''')

        c.execute('PRAGMA foreign_keys = ON')

        c.execute("insert into contacts(id, name, mom) values(1, 'Marge', null)")
        c.execute("insert into contacts(id, name, mom) values(2, 'Bart', 1)")

        with self.assertRaises(sqlite3.IntegrityError):
            c.execute("insert into contacts(id, name, mom) values(3, 'devil', 100)")

        conn.commit()
        conn.close()

    def test_constraints_high_level(self):
        """
        this should fail, but doesn't because of deferred constraints checking:
        https://github.com/django/django/blame/803840abf7dcb6ac190f021a971f1e3dc8f6792a/django/db/backends/sqlite3/schema.py#L16

        In the related issue Simon Charette explicitly requests the defered checking
        https://code.djangoproject.com/ticket/14204#comment:19

        actually the deferred behavior is needed by loading fixtures with incorrect order of inserts or with object pointing to itself

        However, in test is should not be check at the end of the test as discussed in https://code.djangoproject.com/ticket/11665

        Related stack overflow question https://stackoverflow.com/questions/42238857/how-can-i-enable-foreign-key-checks-in-pytest-using-sqllite/53194777#53194777
        """
        marge = Person.objects.create(name='Marge')
        Person.objects.create(name='Bart', mom=marge)
        ids = list(Person.objects.values_list('id', flat=True).order_by('id'))
        biggest_id = ids[-1]

        with self.assertRaises(sqlite3.IntegrityError):
            Person.objects.create(name='devil', mom_id=biggest_id + 1)

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top