Hello, In SQL, defining a unique index on a nullable column will only enforce unicity of non-null values. This behavior seems more useful than allowing exactly one null value.
Your example adds two more columns to the index. Other than that, it’s the exact same situation. In my opinion, the conclusion here is “users of Django still need to know a little bit about SQL”. I don’t think this warrants making code changes. We could add something to the documentation. -- Aymeric. > On 28 Apr 2016, at 20:34, Rich Rauenzahn <rraue...@gmail.com> wrote: > > > I just got bitten by this today, finding a duplicate row where I didn't > expect one. I haven't been able to find an existing Django bug. > > It's a popular topic on stack overflow: > > http://stackoverflow.com/questions/17510261/django-unique-together-constraint-failure > http://dba.stackexchange.com/questions/9759/postgresql-multi-column-unique-constraint-and-null-values > > This is apparently an expected (and standardized) thing in SQL that ('A', > 'B', NULL) is unique to ('A', 'B', NULL) as NULL is never equal to another > NULL. > > There is a workaround at the SQL level of ... > > CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL; > > I'm wondering if this ought to at least be addressed in a runtime warning, or > at least documentation in unique_together -- and I'm hoping that perhaps a > Django level workaround could be devised to explicitly ask for unique indexes > accommodating null values. > > For myself, I'm writing a unittest to fail if any of my unique_together's > have a nullable field and using a specific value as my "null" value for now. > > Thoughts? Has this come up before? > > > > -- > You received this message because you are subscribed to the Google Groups > "Django developers (Contributions to Django itself)" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to django-developers+unsubscr...@googlegroups.com > <mailto:django-developers+unsubscr...@googlegroups.com>. > To post to this group, send email to django-developers@googlegroups.com > <mailto:django-developers@googlegroups.com>. > Visit this group at https://groups.google.com/group/django-developers > <https://groups.google.com/group/django-developers>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-developers/566d247e-4aae-429e-9cc3-2544c82ce9a3%40googlegroups.com > > <https://groups.google.com/d/msgid/django-developers/566d247e-4aae-429e-9cc3-2544c82ce9a3%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/92297BB3-F01E-42C7-8F18-8FCB32F927EC%40polytechnique.org. For more options, visit https://groups.google.com/d/optout.
