英文:
Unique_together doesn't work if a field is null. How to constrain it?
问题
我有一个像这样的模型：
class ProductCode(models.Model):
class Meta:
unique_together = [('code', 'customer'), ]
customer = models.ForeignKey('customers.Customer', models.CASCADE, null=True, blank=True)
code = models.CharField(max_length=20)
product = models.ForeignKey(Product, models.CASCADE, )
...
问题是，如果customer为None（数据库中的空值），则不会强制执行约束。它允许我多次存储具有类似(code='foo', customer=None)的行/对象。我在这个SO帖子中找到了一个建议，该帖子表明对于Postgres用户来说，这个问题是无法修复的。
是否有任何方法可以使用（相对较新的）Meta constraints 来强制执行此约束？我发现文档有点难以理解。
还是我必须实现一个占位符客户来关联“默认”产品代码？
或者，另一种方法是在code之前添加customer的文本表示，然后将其设置为unique=True，在必要时使用__startswith和__contains查询。
英文:
I have a model like this
class ProductCode( models.Model):
class Meta:
unique_together=[('code','customer'), ]
customer = models.ForeignKey( 'customers.Customer', models.CASCADE, null=True, blank=True, )
code = models.CharField( max_length = 20)
product = models.ForeignKey( Product, models.CASCADE, )
...
The trouble is that if customer is None (DB null) then the constraint is not enforced. It lets me store rows/objects with, say, (code='foo', customer=None) multiple times. I have found this SO post which suggests that the problem is un-fixable for Postgres users.
Is there any way to enforce this constraint with the (relatively) new Meta constraints? I am finding the documentation a bit hard to fathom.
Or do I have to implement a placeholder customer to attach "default" product codes to?
Or, another way would be to prepend a textual representation of customer to code and then just make it unique=True and use __startswith= and __contains lookups where necessary.
答案1
得分: 0
这是预期行为：在数据库中，NULL 在某种意义上非常特殊，因为 NULL 不等于非 NULL。
您可以添加第二个唯一性约束，用于在其他字段为 NULL 时使用，如下所示：
from django.db.models import Q
class ProductCode(models.Model):
customer = models.ForeignKey(
'customers.Customer',
models.CASCADE,
null=True,
blank=True,
)
code = models.CharField(max_length=20)
product = models.ForeignKey(
Product,
models.CASCADE,
)
class Meta:
constraints = [
models.UniqueConstraint(
fields=('code', 'customer'), name='code_customer_unique1'
),
models.UniqueConstraint(
fields=('code',),
condition=Q(customer=None),
name='code_customer_unique2',
),
]
注意: 正如关于
unique_together的文档所述，unique_together约束可能会被弃用。文档建议使用 Django 的UniqueConstraint从 Django 的约束框架。
英文:
This is expected behavior: in a database NULL is quite special in the sense that NULL is not equal not NULL.
You can add a second uniqness constraint that is used when the other field is NULL, so:
<pre><code>from django.db.models import Q
class ProductCode(models.Model):
customer = models.ForeignKey(
'customers.Customer',
models.CASCADE,
null=True,
blank=True,
)
code = models.CharField(max_length=20)
product = models.ForeignKey(
Product,
models.CASCADE,
)
class Meta:
constraints = [
models.UniqueConstraint(
fields=('code', 'customer'), name='code_customer_unique<sub>1</sub>'
),
models.UniqueConstraint(
fields=('code',),
<b>condition=Q(customer=None)</b>,
name='code_customer_unique<sub>2</sub>',
),
]</code></pre>
> Note: As the documentation on unique_together <sup>[Django-doc]</sup> says, the unique_together constraint will likely become deprecated. The documentation advises to use the UniqueConstraint <sup>[Django-doc]</sup> from Django's constraint
> framework.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论