#11964 closed New feature (fixed)
Add the ability to use database-level CHECK CONSTRAINTS
Reported by: | Matthew Schinckel | Owned by: | Ian Foote |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | check constraint |
Cc: | Danilo Bargen, esigra, gam_phon, python@…, Ryan Hiebert, Michael Wheeler, Pablo Montepagano, Ties de Kock | 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
I mentioned adding database level CHECK CONSTRAINTS in a post on django-developers.
http://groups.google.com/group/django-developers/browse_thread/thread/38937992972c7808
Attached to this ticket is a patch that provides the functionality listed in the first post of this thread. This is not complete: it only allows simple constraints, and is missing a complete test suite.
Attachments (1)
Change History (43)
by , 15 years ago
Attachment: | check_constraints.diff added |
---|
comment:1 by , 15 years ago
Triage Stage: | Unreviewed → Someday/Maybe |
---|
If we do this, it should be tied into the model validation framework in some way.
comment:2 by , 15 years ago
Agreed. The initial patch was made before the changes with model validation had hit. I will get back to this at some stage.
comment:3 by , 14 years ago
Severity: | → Normal |
---|---|
Type: | → New feature |
comment:6 by , 13 years ago
Cc: | added |
---|
comment:7 by , 10 years ago
Patch needs improvement: | set |
---|
comment:8 by , 9 years ago
Cc: | added |
---|
comment:9 by , 9 years ago
Summary: | Using database-level CHECK CONSTRAINTS → Add the ability to use database-level CHECK CONSTRAINTS |
---|---|
Triage Stage: | Someday/Maybe → Accepted |
Version: | 1.1 → master |
comment:10 by , 9 years ago
Cc: | added |
---|
comment:11 by , 9 years ago
Could/should this feature request also handle this case of constraint I brought up in this thread?
https://code.djangoproject.com/ticket/11964
Basically unique_together doesn't consider a null value unique per SQL spec -- which makes perfect sense in single column indexes and is extended to multi column indexes.
So if someone wanted (A, B, NULL) to be a unique row there would need to be an index built on the first two columns "where <third column> is null"
These SO articles give a better overview:
http://stackoverflow.com/questions/17510261/django-unique-together-constraint-failure
http://dba.stackexchange.com/questions/9759/postgresql-multi-column-unique-constraint-and-null-values
comment:13 by , 8 years ago
Cc: | added |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:16 by , 8 years ago
Needs documentation: | set |
---|
comment:17 by , 7 years ago
Cc: | added |
---|
comment:18 by , 7 years ago
Needs documentation: | unset |
---|---|
Patch needs improvement: | unset |
comment:19 by , 7 years ago
Patch needs improvement: | set |
---|
comment:20 by , 7 years ago
This external project seems to implement this feature:
https://github.com/rapilabs/django-db-constraints
It does some monkey patching that would be unnecessary if this were in core.
comment:21 by , 7 years ago
Patch needs improvement: | unset |
---|
comment:22 by , 7 years ago
Now that Django 2 is released, I'd like to see this land. I think I've resolved all the outstanding issues raised on the pull request before. Is the next step to rebase/squash my work in progress commits?
comment:23 by , 7 years ago
Patch needs improvement: | set |
---|
conflicts needed to be resolved and squash for final review I believe.
comment:25 by , 7 years ago
Cc: | added |
---|
comment:26 by , 7 years ago
Patch needs improvement: | set |
---|
comment:27 by , 7 years ago
Patch needs improvement: | unset |
---|
Please don't check "Patch needs improvement" only because there's a small merge conflict (as far as I can see, there's no outstanding review comments on the patch).
comment:28 by , 6 years ago
Cc: | added |
---|
comment:29 by , 6 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:30 by , 6 years ago
Cc: | added |
---|---|
Keywords: | constraint added; contsraint removed |
comment:32 by , 6 years ago
Is it now possible to define a field that comes with a check constraint, or can they only be defined at the table level? I'd like to be able to define my own StrictlyPositiveIntegerField.
comment:36 by , 6 years ago
It appears that adding multiple 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
, 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.
Should I file a new bug for this?
comment:37 by , 6 years ago
Yeah please file a new ticket Scott and escalate it to release blocker as it's a bug in a yet unreleased version of Django.
This is probably due to the fact SQLite requires a table rebuild on most ALTER TABLE
operations.
comment:40 by , 6 years ago
Is it now possible to define a field that comes with a check constraint, or can they only be defined at the table level? I'd like to be able to define my own StrictlyPositiveIntegerField.
Looks like positive integer fields have been already defined that way:
https://github.com/django/django/blob/084536a/django/db/models/fields/__init__.py#L637
https://github.com/django/django/blob/084536a/django/db/backends/postgresql/base.py#L95
Similarly, one can add a simple custom field (that has no additional arguments):
class StrictlyPositiveIntegerField(models.PositiveIntegerField): # NB the db_check method is not documented def db_check(self, connection): data = self.db_type_parameters(connection) return '"%(column)s" > 0' % data
With a few tweaks it probably could be made to work with custom field args and CheckConstraints too:
BaseDatabaseSchemaEditor.sql_create_check = ( # need to drop the check first, otherwise it can't be modified: 'ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(name)s;' 'ALTER TABLE %(table)s ADD CONSTRAINT %(name)s CHECK (%(check)s)' ) class CustomFieldWithArgsAndChecks(models.Field): ... # handle custom args in __init__() and deconstruct() and define validators too def db_check(self, connection): constraint = models.CheckConstraint(check=models.Q(**{ f'{self.name}__gte': self.gte, f'{self.name}__lte': self.lte, }), name='') with connection.schema_editor() as schema_editor: return constraint._get_check_sql(self.model, schema_editor)
I wonder if there are any plans to officially support doing that kind of thing in the future.
Perhaps it isn't too far out of reach now with CheckConstraints in place.
Contains patches against trunk that enable check constraints