[Django] #29868: Database Check Constraints Not Retained (Only Last Is Stored)
Django
-------------------------------------+-------------------------------------
Reporter: Scott | Owner: nobody
Stevens |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) |
Severity: Release | Keywords: check constraint
blocker |
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29868>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: check constraint | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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!
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: check constraint | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:2>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Simon Charette
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Stevens | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: check constraint | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: check constraint => check constraint sqlite
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Stevens | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: check constraint | Triage Stage: Accepted
sqlite |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
https://github.com/django/django/pull/10539
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Stevens | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
sqlite | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Stevens | Owner: Simon
| Charette
Component: Database layer | Version: master
(models, ORM) |
Keywords: check constraint | Triage Stage: Ready for
sqlite | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"95bda03f2da15172cf342f13ba8a77c007b63fbb" 95bda03]:
{{{
#!CommitTicketReference repository=""
revision="95bda03f2da15172cf342f13ba8a77c007b63fbb"
Fixed #29868 -- Retained database constraints on SQLite table rebuilds.
Refs #11964.
Thanks Scott Stevens for testing this upcoming feature and the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Scott Stevens | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: check constraint | Triage Stage: Ready for
sqlite | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"630f3d8b466570cb13c3f998007e1d9d7b9a5f9b" 630f3d8b]:
{{{
#!CommitTicketReference repository=""
revision="630f3d8b466570cb13c3f998007e1d9d7b9a5f9b"
Refs #29868 -- Prevented name collisions between test constraints on
Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29868#comment:8>