Re: unique_together does not work as expected with nullable fields

[Rich Rauenzahn]

On Friday, April 29, 2016 at 11:00:37 AM UTC-7, Aymeric Augustin wrote:
>
> Hi Rich,
>
> On 29 Apr 2016, at 19:52, Rich Rauenzahn  
> wrote:
>
>
> I see now that I need to provide a sentinel value -- 
> BOO=True,VAL=, or manually create additional unique indexes.
>
>
> Indeed, you should write a migration with a RunSQL operation that creates 
> a unique index on boo where boo = true. Then you can have only one row with 
> boo = True.
>
 

It sounds like my request should probably be appended to this ticket, then: 
 https://code.djangoproject.com/ticket/11964 (Add the ability to use 
database-level CHECK CONSTRAINTS)

-- 
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/7bfd2862-72bc-4df7-bded-a9513fe009b1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: unique_together does not work as expected with nullable fields

[Aymeric Augustin]

Hi Rich,

> On 29 Apr 2016, at 19:52, Rich Rauenzahn  wrote:
> 
> I see now that I need to provide a sentinel value -- BOO=True,VAL=, 
> or manually create additional unique indexes.

Indeed, you should write a migration with a RunSQL operation that creates a 
unique index on boo where boo = true. Then you can have only one row with boo = 
True.

-- 
Aymeric

-- 
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/7E3E39A4-A1FB-448C-82A7-9F10EF347902%40polytechnique.org.
For more options, visit https://groups.google.com/d/optout.

Re: unique_together does not work as expected with nullable fields

[Rich Rauenzahn]

On Friday, April 29, 2016 at 2:16:45 AM UTC-7, Anssi Kääriäinen wrote:
>
> If you really, really want an unique index that allows just a single 
> value, you might want to try unique index on (a, b, c) where c is not 
> null, and another unique index on (a, b) where c is null. That might 
> give the results you are looking for, though I haven't tested this. 
>

What I'm suggesting is a way to express that index within Django, similar 
to unique_together (and perhaps a warning in the docs, given the frequency 
is comes up on stackoverflow.)

I see now that since multi column indexes are an extension of a single 
column index, it makes sense -- you'd never want a single column index to 
only have one null value.

-- 
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/c3b2e562-1e1e-4efd-89d2-eb82c4eb13da%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: unique_together does not work as expected with nullable fields

[Rich Rauenzahn]

On Friday, April 29, 2016 at 12:51:31 AM UTC-7, Florian Apolloner wrote:
>
>
> I am not against a note in the docs, but I find the fact that nulls are 
> not "unique" and can exist in an index more than once very useful (fwiw 
> ordering after a column with null can also be interesting across 
> databases). I'd be interested to hear about your use case -- the "general" 
> use case is usually that you have an optional column but want to ensure it 
> is unique as soon as it is filled…
>
>
Let's see if I can explain my use case without having to explain my whole 
domain ... 

I have a Model where it has a boolean field, "BOO".  When "BOO" is False, 
another field "VAL" should have a meaningful value, otherwise NULL.  VAL is 
the only nullable field.  (And yes, the boolean is actually superfluous, 
"VAL" is sufficient for the logic.)

I want the PK of the Model to always be unique combined with BOO=False and 
VAL (and a couple of other non-nullables).  But I also don't want duplicate 
values of PK,BOO=True,VAL=null, which I am currently getting.  Put another 
way, only one row should have PK,Boo=True for each PK, but I can have many 
PK,Bool=False

I see now that I need to provide a sentinel value -- 
BOO=True,VAL=, or manually create additional unique indexes.

Since it is conceivable for Django to create the right indexes to handle 
the null case, it would be nice to somehow be able to explicitly ask for 
what I want expressed in Django.  (unique_together obviously can't change 
its current default behavior.)

Is that helpful?

-- 
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/75e8e4ca-528d-4cce-8072-30f69a5d58f8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: unique_together does not work as expected with nullable fields

[Aymeric Augustin]

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  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 
> .
> 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/566d247e-4aae-429e-9cc3-2544c82ce9a3%40googlegroups.com
>  
> .
> For more options, visit 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.

Re: unique_together does not work as expected with nullable fields

[Florian Apolloner]

Hi,

On Thursday, April 28, 2016 at 11:59:22 PM UTC+2, Rich Rauenzahn wrote:
>
> 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.
>

Yes, though the standard goes even further: every comparision with NULL 
results in false, in that sense NULL is never equal to NULL but also never 
unequal to 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;
>
>
This only creates an index on two columns though, you might still be 
interested in indexing the cases where A, B, C are not null…
 

> I'm wondering if this ought to at least be addressed in a runtime warning,
>

Runtime warnings are the worst imo, if at all a system check.
 

> 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.
>

I am not against a note in the docs, but I find the fact that nulls are not 
"unique" and can exist in an index more than once very useful (fwiw 
ordering after a column with null can also be interesting across 
databases). I'd be interested to hear about your use case -- the "general" 
use case is usually that you have an optional column but want to ensure it 
is unique as soon as it is filled…

Cheers,
Florian

-- 
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/f4c479dc-4aef-4a92-9d5b-16edbe23c23e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.