TestCase doesn't check for foreign key constraints when using sqlite

[Michel Samia]

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 + 100)