Database constraint checks are not retained on sqlite

[Scott Stevens]

This refers to feature #11964, where I initially asked about this.

It appears that adding multiple database constraints to a table results in only the last being stored.

Reviewing the SQL (./manage.py sqlmigrate), I'm seeing each constraint being added by way of ALTER TABLE (rename), CREATE TABLE, INSERT INTO ... SELECT, DROP TABLE, however only the most recent constraint is added each time, so the previous constraint is dropped with the old table when adding the new one.

Using 38f3de86bd0bfa4c9b57db1237fa55e9fa88bc6e, Python 3.6.6 (Win10x64) with SQLite database.

[Simon Charette]

Hello Scott,

That looks like a legitimate issue that should be elevated to a release blocker but could you provide a minimal project definition to help contributors reproducing it on their side. Once it's they are able to reproduce this issue can be accepted.

Thanks!

[Scott Stevens]

Here's my models.py (everything else is a default django-admin startproject djangoproject . followed by ./manage.py startapp example, with 'example' added to INSTALLED_APPS):

from django.db import models
from django.db.models import Q
from django.core.validators import MinValueValidator


CHOICES = [
    (0, 'A'),
    (1, 'B'),
    (2, 'C'),
]


class ExampleModel(models.Model):
    # Auto PK
    # No constraint generated by MinValueValidator, so I add my own.
    counter = models.PositiveIntegerField(validators=[MinValueValidator(1)])
    dropdown = models.PositiveSmallIntegerField(choices=CHOICES)

    class Meta:
        constraints = [
            models.CheckConstraint(
                check=Q(counter__gt=0), name="counter_bounds"),

            models.CheckConstraint(
                check=Q(dropdown__in=[choice[0] for choice in CHOICES]),
                name="dropdown_choices"),
        ]

Here's the result of ./manage.py sqlmigrate example 0001:

BEGIN;
--
-- Create model ExampleModel
--
CREATE TABLE "example_examplemodel" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "counter" integer unsigned NOT NULL CHECK ("counter" >= 0), "dropdown" smallint unsigned NOT NULL CHECK ("dropdown" >= 0));
--
-- Create constraint counter_bounds on model examplemodel
--
ALTER TABLE "example_examplemodel" RENAME TO "example_examplemodel__old";
CREATE TABLE "example_examplemodel" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "counter" integer unsigned NOT NULL CHECK ("counter" >= 0), "dropdown" smallint unsigned NOT NULL CHECK ("dropdown" >= 0), CONSTRAINT "counter_bounds" CHECK ("counter" > 0));
INSERT INTO "example_examplemodel" ("id", "counter", "dropdown") SELECT "id", "counter", "dropdown" FROM "example_examplemodel__old";
DROP TABLE "example_examplemodel__old";
--
-- Create constraint dropdown_choices on model examplemodel
--
ALTER TABLE "example_examplemodel" RENAME TO "example_examplemodel__old";
CREATE TABLE "example_examplemodel" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "counter" integer unsigned NOT NULL CHECK ("counter" >= 0), "dropdown" smallint unsigned NOT NULL CHECK ("dropdown" >= 0), CONSTRAINT "dropdown_choices" CHECK ("dropdown" IN (0, 1, 2)));
INSERT INTO "example_examplemodel" ("id", "counter", "dropdown") SELECT "id", "counter", "dropdown" FROM "example_examplemodel__old";
DROP TABLE "example_examplemodel__old";
COMMIT;

I can then (using the shell) save an object ExampleModel(counter=0, dropdown=2), but not ExampleModel(counter=1, dropdown=3).

Notably, all constraints are included when I am altering fields on the table after the initial migration, so it seems that adding constraints simply doesn't keep track of previous constraints to include on the CREATE TABLE query.

This example was reproduced with commit 19126339f307e589f99259ab0176c4367a8055f0.

[Simon Charette]

Thank you for your extra details. I confirmed the issue only affects SQLite and is due to how we have to remake the table on this backend. Currently working on a patch.

[Simon Charette]

​https://github.com/django/django/pull/10539

[Carlton Gibson <carlton.gibson@…>]

In 95bda03:

Fixed #29868 -- Retained database constraints on SQLite table rebuilds.

Refs #11964.

Thanks Scott Stevens for testing this upcoming feature and the report.

[GitHub <noreply@…>]

In 630f3d8b:

Refs #29868 -- Prevented name collisions between test constraints on Oracle.