postgresql Django 唯一的一起约束失败？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17510261/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django unique together constraint failure?
提问by Derek
Using Django 1.5.1. Python 2.7.3.
使用 Django 1.5.1。蟒蛇 2.7.3。
I wanted to do a unique together constraint with a foreign key field and a slug field. So in my model meta, I did
我想用一个外键字段和一个 slug 字段做一个唯一的一起约束。所以在我的模型元中，我做到了
foreign_key = models.ForeignKey("self", null=True, default=None)
slug = models.SlugField(max_length=40, unique=False)
class Meta:
unique_together = ("foreign_key", "slug")
I even checked the table description in Postgres (9.1) and the constraint was put into the database table.
我什至检查了 Postgres (9.1) 中的表描述，并将约束放入数据库表中。
-- something like
"table_name_foreign_key_id_slug_key" UNIQUE CONSTRAINT, btree (foreign_key_id, slug)
However, I could still save into the database table a foreign_key of None/null and duplicate strings.
但是，我仍然可以将 None/null 的外键和重复字符串保存到数据库表中。
For example,
例如，
I could input and save
我可以输入并保存
# model objects with slug="python" three times; all three foreign_key(s)
# are None/null because that is their default value
MO(slug="python").save()
MO(slug="python").save()
MO(slug="python").save()
So after using unique_together, why can I still input three of the same valued rows?
那么在使用unique_together之后，为什么我仍然可以输入三个相同值的行？
I'm just guessing right now that it might have to do with the default value of None for the foreign_key field, because before the unique_together, when I just had unique=True on slug, everything worked fine. So if that is the case, what default value should I have that indicates a null value, but also maintains the unique constraint?
我现在只是猜测它可能与外键字段的默认值 None 有关，因为在 unique_together 之前，当我在 slug 上设置 unique=True 时，一切正常。因此，如果是这种情况，我应该使用什么默认值来指示空值，但同时保持唯一约束？
回答by Yossi
In Postgresql NULLisn't equal to any other NULL. Therefore the rows you create are not the same (from Postgres' perspective).
在 PostgresqlNULL中不等于任何其他NULL. 因此，您创建的行并不相同（从 Postgres 的角度来看）。
Update
更新
You have a few ways to deal with it:
你有几种方法来处理它：
- Forbid the
Nullvalue for foreign key and use some default value - Override the
savemethod of your model to check that no such row exists - Change SQL standard :)
- 禁止
Null外键的值并使用一些默认值 - 覆盖
save模型的方法以检查不存在这样的行 - 更改 SQL 标准 :)
回答by Toff'
Add a cleanmethod to your model, so you can edit an existing row.
clean向模型添加方法，以便您可以编辑现有行。
def clean(self):
queryset = MO.objects.exclude(id=self.id).filter(slug=self.slug)
if self.foreign_key is None:
if queryset.exists():
raise ValidationError("A row already exists with this slug and no key")
else:
if queryset.filter(foreign_key=self.foreign_key).exists():
raise ValidationError("This row already exists")
Beware, clean(or full_clean) isn't called by the default savemethod.
当心，clean(或full_clean) 不会被默认save方法调用。
NB: if you put this code in the savemethod, update forms (like in the admin) won't work: you will have a traceback error due to the ValidationErrorexception.
注意：如果您将此代码放在save方法中，更新表单（如在管理员中）将不起作用：由于ValidationError异常，您将遇到回溯错误。
回答by marcinn
Just manually create secondary index on slugfield, but only for NULL values in foreign_key_id:
只需在slug字段上手动创建二级索引，但仅限于 NULL 值foreign_key_id：
CREATE INDEX table_name_unique_null_foreign_key
ON table_name (slug) WHERE foreign_key_id is NULL
Please note, that Django does not support this, so without custom form/model validation you will get pure IntegrityError / 500.
请注意，Django 不支持此功能，因此如果没有自定义表单/模型验证，您将获得纯 IntegrityError / 500。
Possible duplicate of Create unique constraint with null columns
使用空列创建唯一约束的可能重复项
回答by sv_rancher
As hobbyte mentioned, "In Postgresql NULL isn't equal to any other NULL. Therefore the rows you create are not the same (from Postgres' perspective)."
正如 hobbyte 所提到的，“在 Postgresql 中 NULL 不等于任何其他 NULL。因此您创建的行是不一样的（从 Postgres 的角度来看）。”
Another possible way to address this challenge is to add custom validation at the view level in the form_valid method.
解决此挑战的另一种可能方法是在 form_valid 方法中的视图级别添加自定义验证。
In views.py:
在views.py中：
def form_valid(self, form):
--OTHER VALIDATION AND FIELD VALUE ASSIGNMENT LOGIC--
if ModelForm.objects.filter(slug=slug,foreign_key=foreign_key:
form.add_error('field',
forms.ValidationError( _("Validation error message that shows up in your form. "),
code='duplicate_row', ))
return self.form_invalid(form)This approach is helpful if you are using class based views, especially if you are automatically assigning values to fields that you want to hide from the user.
如果您使用基于类的视图，这种方法很有用，尤其是当您自动为要对用户隐藏的字段分配值时。
Pros:
优点：
- You don't have to create dummy default values in the database
- You can still use update forms (see Toff's answer)
- 您不必在数据库中创建虚拟默认值
- 您仍然可以使用更新表单（参见 Toff 的回答）
Cons: - This doesn't protect against duplicate rows created directly at the database level. - If you use Django's admin backend to create new MyModel objects, you'll need to add this same validation logic to your admin form.
缺点： - 这不能防止直接在数据库级别创建的重复行。- 如果您使用 Django 的后台管理来创建新的 MyModel 对象，您需要将相同的验证逻辑添加到您的管理表单中。
