Unique and null

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Unique and null

Mike Dewhirst-3
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

     arn = models.CharField(
         max_length=MEDIUM,
         unique=True,
         null=True,
         blank=True,
     )

Is this correct behaviour?

Thanks

Mike



--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Just
ask and I'll send it to you. Your email software can handle signing.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/fb02525d-773c-4ce3-43a2-f41397176a36%40dewhirst.com.au.

OpenPGP_signature (505 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Ryan Nowakowski
I'm not sure about the case with null. However, I believe that most uses of CharField leave null=False and use the empty string instead to represent "empty" value.

On April 7, 2021 1:06:44 AM CDT, Mike Dewhirst <[hidden email]> wrote:
I have just made a CharField unique and Django detects duplicates nicely 
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike


--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8774A769-9A6D-4BC7-AD3B-2EEDC6C5DF15%40fattuba.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

gamesbook
In reply to this post by Mike Dewhirst-3
Are you using MySQL?

https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints


On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Just
ask and I'll send it to you. Your email software can handle signing.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/efd40f22-50d8-4250-9738-95d2152e68ecn%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Vkash Poudel
I'm using Postgresql 
does CharField unique solves problem of uniqueness ,
can we work with uuid model.
 

On Wed, Apr 7, 2021 at 6:51 PM Derek <[hidden email]> wrote:
Are you using MySQL?



On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Just
ask and I'll send it to you. Your email software can handle signing.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/efd40f22-50d8-4250-9738-95d2152e68ecn%40googlegroups.com.


--
Best Regards,
Bikash Poudel

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAE0sOGzZbTGgvQoo3zHPc3GmDriCrzCDHKs6NUmxc1F21gG-cQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Roger Gammans-2
In reply to this post by gamesbook
Hi All,

Let shed some actually light on the whole problem, before people start piling in.

This is mostly (form the Django perspective) and issue with CharFields, because a CharField can store None or ''. For values of '' in CharField, IIRC, django will convert them to null, with null=True, blank=True. But not with blank=True,null=False.

So if it stores '', (with null=False) then the uniqueness should work as expected.

It is should (rather than will), because Django relies on the backend Database to enforce the constraints, so will depend on the level of conformance the Backend Db has.

Now SQL Specifies that two variables (or a literal and a field) which are None will ALWAYS compare as non-equal, and this stackoverflow ( https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls/767702#767702 ) states the same concept holds for uniqeness .

SQLite, MySQL, Postgres all work, like this, but SQL Server apparently doesn't.

There's quite a bit there; but I hope it helps and isn't too confusing.

On Wed, 2021-04-07 at 06:05 -0700, Derek wrote:
Are you using MySQL?

https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints


On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2b5b6c7e1fe015bb1e8c4f28ded77e70cd3ae988.camel%40gammascience.co.uk.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Roger Gammans-2
I should have said SQL specifies NULL compares Non-equal, the ORM converts None's in python to NULL in SQL for you, as well as all the other things it does

On Wed, 2021-04-07 at 15:25 +0100, Roger Gammans wrote:
Hi All,

Let shed some actually light on the whole problem, before people start piling in.

This is mostly (form the Django perspective) and issue with CharFields, because a CharField can store None or ''. For values of '' in CharField, IIRC, django will convert them to null, with null=True, blank=True. But not with blank=True,null=False.

So if it stores '', (with null=False) then the uniqueness should work as expected.

It is should (rather than will), because Django relies on the backend Database to enforce the constraints, so will depend on the level of conformance the Backend Db has.

Now SQL Specifies that two variables (or a literal and a field) which are None will ALWAYS compare as non-equal, and this stackoverflow ( https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls/767702#767702 ) states the same concept holds for uniqeness .

SQLite, MySQL, Postgres all work, like this, but SQL Server apparently doesn't.

There's quite a bit there; but I hope it helps and isn't too confusing.

On Wed, 2021-04-07 at 06:05 -0700, Derek wrote:
Are you using MySQL?

https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints


On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2a82e569667b2d2052614e15ba1b3ae8592a5734.camel%40gammascience.co.uk.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Mike Dewhirst-3
In reply to this post by gamesbook
No. PostgreSQL.

I checked that post. And googled the answer. Postgres follows the SQL standard and permits multiple nulls in a unique column.

I should have done that instead of asking. I had first thought of it being a Django thing and couldn't find the answer in the docs.

Thanks for responding.

Cheers

Mike



--
(Unsigned mail from my phone)



-------- Original message --------
From: Derek <[hidden email]>
Date: 7/4/21 23:05 (GMT+10:00)
To: Django users <[hidden email]>
Subject: Re: Unique and null

Are you using MySQL?

https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints


On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Just
ask and I'll send it to you. Your email software can handle signing.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/efd40f22-50d8-4250-9738-95d2152e68ecn%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/606e377e.1c69fb81.2c612.06e5SMTPIN_ADDED_MISSING%40gmr-mx.google.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Ing.Daniel Bojorge
I suggest that if you set unique, the field must have value, so null=False.

Blank=True es for form and template, to set required.  Null es for database.



Mi Blog
Nicaragua

"Si ustedes permanecen unidos a mí, y si permanecen fieles a mis enseñanzas, pidan lo que quieran y se les dará.
(Juan 15:7 DHH)
Bendito el varón que se fía en el SEÑOR, y cuya confianza es el SEÑOR.
(Jeremías 17:7 RV2000)




El mié, 7 abr 2021 a las 16:52, Mike Dewhirst (<[hidden email]>) escribió:
No. PostgreSQL.

I checked that post. And googled the answer. Postgres follows the SQL standard and permits multiple nulls in a unique column.

I should have done that instead of asking. I had first thought of it being a Django thing and couldn't find the answer in the docs.

Thanks for responding.

Cheers

Mike



--
(Unsigned mail from my phone)



-------- Original message --------
From: Derek <[hidden email]>
Date: 7/4/21 23:05 (GMT+10:00)
To: Django users <[hidden email]>
Subject: Re: Unique and null

Are you using MySQL?



On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Just
ask and I'll send it to you. Your email software can handle signing.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/efd40f22-50d8-4250-9738-95d2152e68ecn%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/606e377e.1c69fb81.2c612.06e5SMTPIN_ADDED_MISSING%40gmr-mx.google.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAMQeQjbA%3D6GG--TwG3ja8L%2B-2%2BtRnMfH3_jrPvrZ%2B5tTgPmGYA%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Mike Dewhirst-3
Many thanks

Mike



--
(Unsigned mail from my phone)



-------- Original message --------
From: "Ing.Daniel Bojorge" <[hidden email]>
Date: 8/4/21 08:59 (GMT+10:00)
Subject: Re: Unique and null

I suggest that if you set unique, the field must have value, so null=False.

Blank=True es for form and template, to set required.  Null es for database.



Dios L@s Bendiga

Saludos,

 
<img height="0" width="0" style="margin:0px;padding:0px;border:0px none;outline:currentcolor none 0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;display:block;width:0px;height:0px;overflow:hidden" alt="--" onmouseover="imageMousePointerUpdate(true)" onmouseout="imageMousePointerUpdate(false)">
<img height="70" width="105" style="margin:0px;padding:0px;border:1px solid rgb(238,238,238);outline:currentcolor none 0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;display:block" alt="" src="http://d3mod6n032mdiz.cloudfront.net/thumb2/d/a/n/daniel.bojorge/daniel.bojorge-105x70.jpg" onmouseover="imageMousePointerUpdate(true)" onmouseout="imageMousePointerUpdate(false)">
 
daniel.bojorge
<img height="0" width="0" style="margin:0px;padding:0px;border:0px none;outline:currentcolor none 0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;display:block;width:0px;height:0px;overflow:hidden" alt="http://" onmouseover="imageMousePointerUpdate(true)" onmouseout="imageMousePointerUpdate(false)">about.me/daniel.bojorge
 
 

<img style="border-style:none;height:1em;width:1em;background-color:transparent;max-width:none;vertical-align:text-top" src="https://github.githubassets.com/images/icons/emoji/unicode/1f40d.png" width="20" height="20" alt="snake" onmouseover="imageMousePointerUpdate(true)" onmouseout="imageMousePointerUpdate(false)">Curso Desarrollo Web con Python usando Django 2.2 Para Principiantes con Descuento 95%   <img style="font-family:&quot;Apple Color Emoji&quot;,&quot;Segoe UI&quot;,&quot;Segoe UI Emoji&quot;,&quot;Segoe UI Symbol&quot;;font-size:1.2em;font-weight:400;background-color:transparent;border-style:none;height:1em;width:1em;max-width:none;vertical-align:text-top" src="https://github.githubassets.com/images/icons/emoji/unicode/1f40d.png" width="20" height="20" alt="snake" onmouseover="imageMousePointerUpdate(true)" onmouseout="imageMousePointerUpdate(false)">

Mi Blog
Nicaragua

"Si ustedes permanecen unidos a mí, y si permanecen fieles a mis enseñanzas, pidan lo que quieran y se les dará.
(Juan 15:7 DHH)
Bendito el varón que se fía en el SEÑOR, y cuya confianza es el SEÑOR.
(Jeremías 17:7 RV2000)




El mié, 7 abr 2021 a las 16:52, Mike Dewhirst (<[hidden email]>) escribió:
No. PostgreSQL.

I checked that post. And googled the answer. Postgres follows the SQL standard and permits multiple nulls in a unique column.

I should have done that instead of asking. I had first thought of it being a Django thing and couldn't find the answer in the docs.

Thanks for responding.

Cheers

Mike



--
(Unsigned mail from my phone)



-------- Original message --------
From: Derek <[hidden email]>
Date: 7/4/21 23:05 (GMT+10:00)
To: Django users <[hidden email]>
Subject: Re: Unique and null

Are you using MySQL?



On Wednesday, 7 April 2021 at 08:07:35 UTC+2 Mike Dewhirst wrote:
I have just made a CharField unique and Django detects duplicates nicely
but seems to ignore multiple instances where the field value is null.

    arn = models.CharField(
        max_length=MEDIUM,
        unique=True,
        null=True,
        blank=True,
    )

Is this correct behaviour?

Thanks

Mike



--
Signed email is an absolute defence against phishing. This email has
been signed with my private key. If you import my public key you can
automatically decrypt my signature and be sure it came from me. Just
ask and I'll send it to you. Your email software can handle signing.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/efd40f22-50d8-4250-9738-95d2152e68ecn%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/606e377e.1c69fb81.2c612.06e5SMTPIN_ADDED_MISSING%40gmr-mx.google.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAMQeQjbA%3D6GG--TwG3ja8L%2B-2%2BtRnMfH3_jrPvrZ%2B5tTgPmGYA%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/606e50a3.1c69fb81.e6035.1501SMTPIN_ADDED_MISSING%40gmr-mx.google.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Ryan Nowakowski
In reply to this post by Roger Gammans-2
On Wed, Apr 07, 2021 at 03:25:53PM +0100, Roger Gammans wrote:
> This is mostly (form the Django perspective) and issue with CharFields,
> because a CharField can store None or ''.  For values of ''
> in  CharField, IIRC, django will convert them to null, with null=True,
> blank=True. But not with blank=True,null=False.See also :
> https://docs.djangoproject.com/en/3.1/ref/models/fields/#null

I read the documentation a bit differently.  My interpretation is that
CharField(null=True) means that both ''(empty string) and None(null) are
valid values.  But no implicit conversion from '' to None takes place.

CharField(null=False) means that None(null) isn't a valid value and that
only ''(empty string) is permitted.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/20210408213628.GL15054%40fattuba.com.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Roger Gammans-2
On Thu, 2021-04-08 at 16:36 -0500, Ryan Nowakowski wrote:

> On Wed, Apr 07, 2021 at 03:25:53PM +0100, Roger Gammans wrote:
> > This is mostly (form the Django perspective) and issue with
> > CharFields,
> > because a CharField can store None or ''.  For values of ''
> > in  CharField, IIRC, django will convert them to null, with
> > null=True,
> > blank=True. But not with blank=True,null=False.See also :
> > https://docs.djangoproject.com/en/3.1/ref/models/fields/#null
>
> I read the documentation a bit differently.  My interpretation is
> that
> CharField(null=True) means that both ''(empty string) and None(null)
> are
> valid values.  But no implicit conversion from '' to None takes
> place.
>
> CharField(null=False) means that None(null) isn't a valid value and
> that
> only ''(empty string) is permitted.

I understand that reading, and the simple answer is we are both right!

See:
https://github.com/django/django/blob/45a58c31e64dbfdecab1178b1d00a3803a90ea2d/django/db/models/fields/__init__.py#L1082

This shows the model field telling the any auto creating form field,
say like in the admin model, which value to prefer 'empty' values.

Since it's not the model field, so any python code which saves '' and
None, directed onto the model will still have those respected as '' and
NULL.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2d0608b7d8c120af35d98c194659de1f89fafd08.camel%40gammascience.co.uk.
Reply | Threaded
Open this post in threaded view
|

Re: Unique and null

Ryan Nowakowski
On Thu, Apr 08, 2021 at 11:12:51PM +0100, Roger Gammans wrote:

> On Thu, 2021-04-08 at 16:36 -0500, Ryan Nowakowski wrote:
> > On Wed, Apr 07, 2021 at 03:25:53PM +0100, Roger Gammans wrote:
> > > This is mostly (form the Django perspective) and issue with
> > > CharFields,
> > > because a CharField can store None or ''.  For values of ''
> > > in  CharField, IIRC, django will convert them to null, with
> > > null=True,
> > > blank=True. But not with blank=True,null=False.See also :
> > > https://docs.djangoproject.com/en/3.1/ref/models/fields/#null
> >
> > I read the documentation a bit differently.  My interpretation is
> > that
> > CharField(null=True) means that both ''(empty string) and None(null)
> > are
> > valid values.  But no implicit conversion from '' to None takes
> > place.
> >
> > CharField(null=False) means that None(null) isn't a valid value and
> > that
> > only ''(empty string) is permitted.
>
> I understand that reading, and the simple answer is we are both right!
>
> See:
> https://github.com/django/django/blob/45a58c31e64dbfdecab1178b1d00a3803a90ea2d/django/db/models/fields/__init__.py#L1082
>
> This shows the model field telling the any auto creating form field,
> say like in the admin model, which value to prefer 'empty' values.
>
> Since it's not the model field, so any python code which saves '' and
> None, directed onto the model will still have those respected as '' and
> NULL.

Whhhhhyyyyyyyy Oracle!!!!!!! *sigh* :)


    find django/db/backends -name "features.py" | xargs grep interprets_empty_strings_as_nulls
    django/db/backends/oracle/features.py:    interprets_empty_strings_as_nulls = True
    django/db/backends/base/features.py:    interprets_empty_strings_as_nulls = False

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/20210408224331.GP15054%40fattuba.com.