Opened 8 months ago

Closed 8 months ago

Last modified 8 months ago

#29868 closed Bug (fixed)

Database constraint checks are not retained on sqlite

Reported by: Scott Stevens Owned by: Simon Charette
Component: Database layer (models, ORM) Version: master
Severity: Release blocker Keywords: check constraint sqlite
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

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.

Change History (8)

comment:1 Changed 8 months ago by 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!

comment:2 Changed 8 months ago by 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.

comment:3 Changed 8 months ago by Simon Charette

Owner: changed from nobody to Simon Charette
Status: newassigned
Triage Stage: UnreviewedAccepted

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.

comment:4 Changed 8 months ago by Simon Charette

Keywords: sqlite added
Summary: Database Check Constraints Not Retained (Only Last Is Stored)Database constraint checks are not retained on sqlite

comment:5 Changed 8 months ago by Simon Charette

Has patch: set

comment:6 Changed 8 months ago by Carlton Gibson

Triage Stage: AcceptedReady for checkin

comment:7 Changed 8 months ago by Carlton Gibson <carlton.gibson@…>

Resolution: fixed
Status: assignedclosed

In 95bda03:

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

Refs #11964.

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

comment:8 Changed 8 months ago by GitHub <noreply@…>

In 630f3d8b:

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

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