Opened 12 years ago

Closed 4 years ago

Last modified 2 years ago

#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)

check_constraints.diff (15.8 KB) - added by Matthew Schinckel 12 years ago.
Contains patches against trunk that enable check constraints

Download all attachments as: .zip

Change History (43)

Changed 12 years ago by Matthew Schinckel

Attachment: check_constraints.diff added

Contains patches against trunk that enable check constraints

comment:1 Changed 12 years ago by Russell Keith-Magee

Triage Stage: UnreviewedSomeday/Maybe

If we do this, it should be tied into the model validation framework in some way.

comment:2 Changed 12 years ago by Matthew Schinckel

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 Changed 11 years ago by Julien Phalip

Severity: Normal
Type: New feature

comment:4 Changed 10 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:5 Changed 10 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:6 Changed 10 years ago by Danilo Bargen

Cc: Danilo Bargen added

comment:7 Changed 7 years ago by Tim Graham

Patch needs improvement: set

comment:8 Changed 7 years ago by esigra

Cc: esigra added

comment:9 Changed 6 years ago by Tim Graham

Summary: Using database-level CHECK CONSTRAINTSAdd the ability to use database-level CHECK CONSTRAINTS
Triage Stage: Someday/MaybeAccepted
Version: 1.1master

comment:10 Changed 6 years ago by gam_phon

Cc: gam_phon added

comment:11 Changed 6 years ago by Rich Rauenzahn

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:12 Changed 6 years ago by alexpirine

I would also love this feature.

comment:13 Changed 5 years ago by Ian Foote

Cc: python@… added
Owner: changed from nobody to Ian Foote
Status: newassigned

comment:14 Changed 5 years ago by Tim Graham <timograham@…>

In 19b2dfd1:

Refs #11964, #26167 -- Made Expressions deconstructible.

comment:15 Changed 5 years ago by Tim Graham <timograham@…>

In 508b5deb:

Refs #11964 -- Made Q objects deconstructible.

comment:16 Changed 5 years ago by Ian Foote

Needs documentation: set

comment:17 Changed 4 years ago by Ryan Hiebert

Cc: Ryan Hiebert added

comment:18 Changed 4 years ago by Asif Saifuddin Auvi

Needs documentation: unset
Patch needs improvement: unset

comment:19 Changed 4 years ago by Tim Graham

Patch needs improvement: set

comment:20 Changed 4 years ago by Luke Plant

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 Changed 4 years ago by Ian Foote

Patch needs improvement: unset

comment:22 Changed 4 years ago by Ian Foote

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 Changed 4 years ago by Asif Saifuddin Auvi

Patch needs improvement: set

conflicts needed to be resolved and squash for final review I believe.

comment:24 Changed 4 years ago by Ian Foote

Patch needs improvement: unset

Conflicts fixed and commits squashed.

comment:25 Changed 4 years ago by Michael Wheeler

Cc: Michael Wheeler added

comment:26 Changed 4 years ago by Asif Saifuddin Auvi

Patch needs improvement: set

comment:27 Changed 4 years ago by Tim Graham

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 Changed 4 years ago by Pablo Montepagano

Cc: Pablo Montepagano added

comment:29 Changed 4 years ago by Carlton Gibson

Triage Stage: AcceptedReady for checkin

comment:30 Changed 4 years ago by Ties de Kock

Cc: Ties de Kock added
Keywords: constraint added; contsraint removed

comment:31 Changed 4 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 952f05a6:

Fixed #11964 -- Added support for database check constraints.

comment:32 Changed 3 years ago by Gavin Wahl

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:33 Changed 3 years ago by Tim Graham <timograham@…>

In 9a0e0d96:

Refs #11964 -- Renamed a database check constraint test.

comment:34 Changed 3 years ago by Tim Graham <timograham@…>

In 9142bebf:

Refs #11964 -- Changed CheckConstraint() signature to use keyword-only arguments.

Also renamed the constraint argument to check to better represent which
part of the constraint the provided Q object represents.

comment:35 Changed 3 years ago by Tim Graham <timograham@…>

In bc7e288:

Fixed #29745 -- Based Expression equality on detailed initialization signature.

The old implementation considered objects initialized with an equivalent
signature different if some arguments were provided positionally instead of
as keyword arguments.

Refs #11964, #26167.

comment:36 Changed 3 years ago by Scott Stevens

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?

Last edited 3 years ago by Scott Stevens (previous) (diff)

comment:37 Changed 3 years ago by Simon Charette

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:38 Changed 3 years ago by Tim Graham <timograham@…>

In 32da3cfd:

Refs #11964 -- Removed raw SQL from and cleaned up constraint operation tests.

comment:39 Changed 3 years ago by 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.

comment:40 Changed 3 years ago by Balazs Endresz

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.

Last edited 3 years ago by Balazs Endresz (previous) (diff)

comment:41 Changed 2 years ago by Mariusz Felisiak <felisiak.mariusz@…>

In 8b3e1b6:

Refs #11964 -- Made constraint support check respect required_db_features.

This will notably silence the warnings issued when running the test
suite on MySQL.

comment:42 Changed 2 years ago by Mariusz Felisiak <felisiak.mariusz@…>

In 306b6875:

Refs #11964 -- Removed SimpleCol in favor of Query(alias_cols).

This prevent having to pass simple_col through multiple function calls
by defining whether or not references should be resolved with aliases
at the Query level.

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