跨多个列实现Django 2.2数据库约束 [英] Implementing Django 2.2 database constraint across multiple columns

问题描述

我有一个带有开始日期/时间和结束日期/时间的Django模型，其中所有四个成分可能(独立地)为空值(并且空值/未知值和已知值之间存在语义差异).我正在尝试实施数据库约束[ 1 ， 2 ]进行检查如果它们不是null，则开始日期/时间早于结束日期/时间.

I have a Django model with start date/time and end date/time where all four components may (independently) be a null value (and there is a semantic difference between a null/unknown value and a known value). I am trying to implement a database constraint [1, 2] to check that if they are non-null that the start date/time is before the end date/time.

我已经通过以下两种不同的方式实现了约束(注释为选项1，一个约束，选项2为两个约束):

I have implemented the constraint in two different ways (commented as Option 1, a single constraint, and Option 2, as two constraints) below:

from django.db import models

class Event( models.Model ):
  start_date = models.DateField( blank = True, null = True )
  start_time = models.TimeField( blank = True, null = True )
  end_date   = models.DateField( blank = True, null = True )
  end_time   = models.TimeField( blank = True, null = True )

  class Meta:
    constraints = [
# Option 1
      models.CheckConstraint(
        check = ( models.Q( start_date__isnull = True )
                | models.Q( end_date__isnull = True )
                | models.Q( start_date__lt = models.F( 'end_date' ) )
                | ( ( models.Q( start_time__isnull = True )
                    | models.Q( end_time__isnull = True )
                    | models.Q( start_time__lte = models.F( 'end_time' ) )
                    )
                    & models.Q( start_date = models.F( 'end_date' ) ) # This line
                  )
                ),
        name  = 'start_date_and_time_lte_end_date_and_time'
      ),

# Option 2
      models.CheckConstraint(
        check = ( models.Q( start_date__isnull = True )
                | models.Q( end_date__isnull = True )
                | models.Q( start_date__lte = models.F( 'end_date' ) )
                ),
        name  = 'start_date_lte_end_date'
      ),
      models.CheckConstraint(
        check = ~( models.Q( start_date = models.F( 'end_date' ) )
                 & models.Q( start_time_gt = models.F( 'end_time' ) )
                 ),
        name  = 'not_start_date_eq_end_date_and_start_time_gt_end_time'
      ),
    ]

当我运行 makemigrations 时，这两个选项都会成功.

When I run makemigrations both options succeed.

对于选项1，当我尝试在测试中使用模型时:

With Option 1, when I try to use the model in a test:

class EventModelTest( TestCase ):
  def test_simple(self):
    obj = Event.objects.create()
    self.assertTrue( isinstance( obj, Event ) )

我得到了错误:

django.db.utils.DatabaseError: malformed database schema (packagename_event) - no such column: new_packagename_event.start_time

如果我注释掉标记为#此行的行，此错误就消失了(但是这样做会使约束函数无法正常工作).

This error goes away if I comment out the line marked # this line (but doing that would make the constraint function incorrectly).

选项2看起来效果很好，但是不太明显它会正确考虑空值.

Option 2 appears to work perfectly but is less obvious that it is going to consider null values correctly.

1. 选项1和选项2中的检查约束是否相等?
2. 为什么选项1失败了，该如何解决?是因为我试图在相同约束条件下比较两个地方的同一列的值，还是有其他原因?

推荐答案

找到了实现约束的第三种方法，这样很显然，可以使用两个约束正确地考虑 NULL 值:

Found a 3rd way to implement the constraints so that it is more obvious that NULL values are being considered correctly using two constraints:

from django.db import models

class Event( models.Model ):
  start_date = models.DateField( blank = True, null = True )
  start_time = models.TimeField( blank = True, null = True )
  end_date   = models.DateField( blank = True, null = True )
  end_time   = models.TimeField( blank = True, null = True )

  class Meta:
    constraints = [
# Option 3
      models.CheckConstraint(
        check = ( models.Q( start_date__isnull = True )
                | models.Q( end_date__isnull = True )
                | models.Q( start_date__lte = models.F( 'end_date' ) )
                ),
        name  = 'start_date_lte_end_date'
      ),
      models.CheckConstraint(
        check = ( models.Q( start_date__isnull = True )
                | models.Q( end_date__isnull = True )
                | models.Q( start_date__lt = models.F( 'end_date' ) )
                | models.Q( start_time__isnull = True )
                | models.Q( end_time__isnull = True )
                | models.Q( start_time__lte = models.F( 'end_time' ) )
                ),
        name  = 'not_start_date_eq_end_date_and_start_time_gt_end_time'
      ),
    ]

在以下情况下，这两个约束将完全重叠:

The two constraints will overlap exactly in the cases when:

• 开始日期为空；
• 结束日期为空；或
• 开始日期<结束日期

• start_date is null;
• end_date is null; or
• start_date < end_date

检查可以通过的其他方式是，当第一个约束是 start_date = end_date 而第二个约束是:

The remaining ways the checks can pass are when the first constraint is start_date = end_date and the second constraint is:

• start_time 为空；
• end_time 为空；或
• 开始时间< =结束时间

• start_time is null;
• end_time is null; or
• start_time <= end_time

匹配选项1中所有可能的情况.

Which matches all the possible cases in Option 1.

在进一步测试中，具有以下约束的模型演示了相同的问题:

On further testing, the model with constraints below demonstrates the same issue:

class SimpleModel( models.Model ):
  value = models.IntegerField()

  class Meta:
    constraints = [
      models.CheckConstraint(
        check = ( models.Q( value__gte = 0 )
                & ( models.Q( value__gte = 0 )
                  | models.Q( value__gte = 0 ) # this line
                  )
                ),
        name  = "simplemodel_check1"
      ),
      models.CheckConstraint(
        check = ( models.Q( value__gte = 0 )
                & ( models.Q( value__gte = 0 )
                  & models.Q( value__gte = 0 )
                  )
                ),
        name  = "simplemodel_check2"
      ),
      models.CheckConstraint(
        check = ( models.Q( value__gte = 0 ) | models.Q( value__gte = 0 ) ),
        name  = "simplemodel_check3"
      ),
      models.CheckConstraint(
        check = ( models.Q( value__gte = 0 ) & models.Q( value__gte = 0 ) ),
        name  = "simplemodel_check4"
      ),
    ]

第二，第三和第四约束没有问题，但是在尝试创建具有错误的模型实例时，第一约束导致异常:

The 2nd, 3rd and 4th constraints work without issue but the 1st constraint causes an exception when trying to create an instance of the model with an error:

django.db.utils.DatabaseError: malformed database schema (packagename_event) - no such column: new_packagename_simplemodel.value

& 和 | 的组合似乎是一个问题.

It appears to be an issue with the combination of & and |.

这篇关于跨多个列实现Django 2.2数据库约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！