在 Django 中允许空值的唯一字段 [英] Unique fields that allow nulls in Django

问题描述

我有带有字段栏的模型 Foo.bar 字段应该是唯一的，但允许其中包含空值，这意味着如果 bar 字段为 null，我想允许多条记录，但如果它不是 null 值必须是唯一的.

I have model Foo which has field bar. The bar field should be unique, but allow nulls in it, meaning I want to allow more than one record if bar field is null, but if it is not null the values must be unique.

这是我的模型:

class Foo(models.Model):
    name = models.CharField(max_length=40)
    bar = models.CharField(max_length=40, unique=True, blank=True, null=True, default=None)

这里是表对应的SQL:

And here is the corresponding SQL for the table:

CREATE TABLE appl_foo
(
    id serial NOT NULL,
     "name" character varying(40) NOT NULL,
    bar character varying(40),
    CONSTRAINT appl_foo_pkey PRIMARY KEY (id),
    CONSTRAINT appl_foo_bar_key UNIQUE (bar)
)   

当使用管理界面创建超过 1 个 bar 为 null 的 foo 对象时，它给我一个错误:带有这个 Bar 的 Foo 已经存在."

When using admin interface to create more than 1 foo objects where bar is null it gives me an error: "Foo with this Bar already exists."

但是当我插入数据库(PostgreSQL)时:

However when I insert into database (PostgreSQL):

insert into appl_foo ("name", bar) values ('test1', null)
insert into appl_foo ("name", bar) values ('test2', null)

这有效，很好，它允许我插入超过 1 条记录且 bar 为空，因此数据库允许我做我想做的事，这只是 Django 模型有问题.有什么想法吗?

This works, just fine, it allows me to insert more than 1 record with bar being null, so the database allows me to do what I want, it's just something wrong with the Django model. Any ideas?

编辑

解决方案的可移植性就 DB 而言不是问题，我们对 Postgres 感到满意.我已经尝试为可调用对象设置唯一值，这是我的函数为 bar 的特定值返回 True/False，它没有给出任何错误，但看起来完全没有效果.

The portability of the solution as far as DB is not an issue, we are happy with Postgres. I've tried setting unique to a callable, which was my function returning True/False for specific values of bar, it didn't give any errors, however seamed like it had no effect at all.

到目前为止，我已经从 bar 属性中删除了唯一说明符并处理了应用程序中的 bar 唯一性，但仍在寻找更优雅的解决方案.有什么推荐吗?

So far, I've removed the unique specifier from the bar property and handling the bar uniqueness in the application, however still looking for a more elegant solution. Any recommendations?

推荐答案

自从ticket #9039 被修复后，Django 没有考虑 NULL 等于 NULL 来进行唯一性检查，参见:

Django has not considered NULL to be equal to NULL for the purpose of uniqueness checks since ticket #9039 was fixed, see:

http://code.djangoproject.com/ticket/9039

这里的问题是表单 CharField 的规范化空白"值是一个空字符串，而不是 None.因此，如果将该字段留空，则会得到一个空字符串，而不是 NULL，存储在数据库中.在 Django 和数据库规则下，空字符串等于用于唯一性检查的空字符串.

The issue here is that the normalized "blank" value for a form CharField is an empty string, not None. So if you leave the field blank, you get an empty string, not NULL, stored in the DB. Empty strings are equal to empty strings for uniqueness checks, under both Django and database rules.

您可以通过为 Foo 提供您自己的自定义模型表单并使用将空字符串转换为 None 的 clean_bar 方法来强制管理界面为空字符串存储 NULL:

You can force the admin interface to store NULL for an empty string by providing your own customized model form for Foo with a clean_bar method that turns the empty string into None:

class FooForm(forms.ModelForm):
    class Meta:
        model = Foo
    def clean_bar(self):
        return self.cleaned_data['bar'] or None

class FooAdmin(admin.ModelAdmin):
    form = FooForm

这篇关于在 Django 中允许空值的唯一字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！