IntegrityError, Duplicate key, MySQL 5, blank and unique
johnsu01
CharFields, that I would like to be optional but if they are filled
out, unique.
I express this with:
tab = meta.CharField(maxlength=15, unique=True, blank=True)
Creating the first object in admin with a blank tab field works just
fine.
However, if I try to create a second object with a blank tab field, I
get an IntegrityError warning me of a duplicate key.
Traceback (most recent call last):
File "/home/fanfatale/src/django/django/core/handlers/base.py" in
get_response
74. response = callback(request, *callback_args, **callback_kwargs)
File
"/home/fanfatale/src/django/django/contrib/admin/views/decorators.py"
in _checklogin
54. return view_func(request, *args, **kwargs)
File "/home/fanfatale/src/django/django/contrib/admin/views/main.py" in
add_stage
408. new_object = manipulator.save(new_data)
File "/home/fanfatale/src/django/django/utils/functional.py" in
_curried
3. return args[0](*(args[1:]+moreargs), **dict(kwargs.items() +
morekwargs.items()))
File
"/home/fanfatale/usr/lib/python2.3/site-packages/django/core/meta/__init__.py"
in manipulator_save
1773. new_object.save()
File "/home/fanfatale/src/django/django/utils/functional.py" in
_curried
3. return args[0](*(args[1:]+moreargs), **dict(kwargs.items() +
morekwargs.items()))
File
"/home/fanfatale/usr/lib/python2.3/site-packages/django/core/meta/__init__.py"
in method_save
1026. ','.join(placeholders)), db_values)
File "/home/fanfatale/src/django/django/core/db/base.py" in execute
10. result = self.cursor.execute(sql, params)
File "/home/fanfatale/src/django/django/core/db/backends/mysql.py" in
execute
32. return self.cursor.execute(sql, params)
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py" in execute
137. self.errorhandler(self, exc, value)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py" in
defaulterrorhandler
33. raise errorclass, errorvalue
IntegrityError at /admin/inventory/textpages/add/
(1062, "Duplicate entry '' for key 3")
As long as I fill out the tab field with a unique string, I can
continue to add objects with no problems.
So it seem like what is happening is that because of the unique
requirement, there can be only one record with an empty string.
My first thought was to ignore the docs and use null=True instead of
blank=True, since in MySQL NULL can't be compared to anything else, so
wouldn't flunk the unique test. But (since this is a CharField?), the
lack of input was still saved as an empty string. Same thing if I tried
both blank=True and null=True at the same time. I did full sqlreset's
between these attempts, and verified through the mysql client that the
changes had taken effect.
Help?
mysql Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i386) using readline
5.0
GNU/Linux, and the mysql-python stuff looks to be Version 1.2.1c3,
Django trunk r2476. And this is all on Dreamhost.
I did not have this problem with MySQL 4 while I was testing on my
laptop.
Ivan Sagalaev
>Using trunk, I have a model that has a number of fields which are
>CharFields, that I would like to be optional but if they are filled
>out, unique.
>
>
I may be well wrong but I don't think you can express this at DB level.
Uniqueness at DB level includes empty value (even NULL) as any other.
Hence you can have only one NULL, only one '', and only one 'anything
else'...
But you can teach Django to check such thing itself by writing a validator.
from django.core import validators
from django import models
class NullOrUnique:
def __init__(self, app_name, model_name, field_name):
self.app_name, self.model_name self.field_name = app_name,
model_name, field_name
def __call__(self, field_data, all_data):
if field_data is not None:
model = models.get_module(self.app_name, self.model_name)
if model.get_list(**{'self.field_name'+'__exact':field_data})
raise validators.ValidatorError('Should be null or unique')
# ...
class MyModel(meta.Model):
tab = meta.CharField(maxlength=15, validator_list =
[NullOrUnique('app_name','mymodels','tab')])
I didn't test it but the idea should be clear.
Also I think there is a better way of getting model and field name
instead of repeating them in parameters but I didn't want to dig that
far :-)
Ivan Sagalaev
> if model.get_list(**{'self.field_name'+'__exact':field_data})
>
>
Ouch :-) Make this 'get_list' to be 'get_count' instead...
Malcolm Tredinnick
> johnsu01 wrote:
>
> >Using trunk, I have a model that has a number of fields which are
> >CharFields, that I would like to be optional but if they are filled
> >out, unique.
> >
> >
> I may be well wrong but I don't think you can express this at DB level.
> Uniqueness at DB level includes empty value (even NULL) as any other.
> Hence you can have only one NULL, only one '', and only one 'anything
> else'...
That is not correct; NULLs are very special, whereas '' and 'anything
else' are exactly the same type of thing (a defined value). More
information than you wanted to know follows...
In a correct SQL implementation, NULL is not comparable to anything,
including another NULL or even itself. That is why, for example, it is
invalid SQL to write a clause like "where foo = NULL", because the test
does not make sense ("where foo is NULL" is the valid form). [Aside: at
this point Oracle can go take a seat in the corner and hang its head in
shame. They won't change '' = NULL from being true to being false or
invalid, for backwards compatibility reasons and yet they still claim
some level of SQL compliance. You could also hear the typing of many
database programmers around mid-2002 when PostgreSQL released a version
that finally removed the foo = NULL comparison and forced everybody to
fix their code (it had been deprecated for a while).]
Similarly, ordering sets of results that contain NULLs is not well-
defined, because ordering or NULLs with respect to defined values is not
specified and you cannot make any comparisons to deduce it yourself. So
different databases give varying results here (even different versions
of the same database) and all are correct.
That being said, database implementations of NULL handling have,
historically, varied wildly. The SQLite website has a pretty decent and
summary of behaviour as of a couple of years ago for a wide range of
databases. (http://www.sqlite.org/nulls.html ). I'm not sure if MS-SQL's
handling of NULLs as distinct has changed (as that page indicates, it
did not used to handle them as distinct) -- that has bitten me
annoyingly in the past as something to work around. Once you get used to
having NULLs behaving logically as a valid unknown or undefined value,
encountering a database that does not honour this requirement is a real
step backwards.
Cheers,
Malcolm
Ivan Sagalaev
>In a correct SQL implementation, NULL is not comparable to anything,
>
>
Yes... It should be :-). And as you said implementation is the whole
another businness. johnsu01 talked about MySQL and given its history of
not very strong adherence to standard but preferring pratical aspects of
doing things I supposed that it's not working like that. The problem
description made it look like that for me. So forgive me for sounding
like some kinf of authority on the matter of uniqueness!
But since things work so different may be custom validator is not a bad
idea after all...
Malcolm Tredinnick
> Malcolm Tredinnick wrote:
>
> >In a correct SQL implementation, NULL is not comparable to anything,
> >
> >
> Yes... It should be :-). And as you said implementation is the whole
> another businness. johnsu01 talked about MySQL and given its history of
> not very strong adherence to standard but preferring pratical aspects of
> doing things I supposed that it's not working like that. The problem
> description made it look like that for me. So forgive me for sounding
> like some kinf of authority on the matter of uniqueness!
I should apologise: I did not mean to come across as slamming your
contribution. After I pressed send, I realised my mail was more abrupt
than necessary.
To the problem at hand: John's problem is not actually about NULLs being
non-distinct. He has bumped into the design decision in Django that says
CharFields will always have empty values stored as empty strings, not
NULLs. Even if null=True is set on that field (this is documented, but
you have to read it carefully).
> But since things work so different may be custom validator is not a bad
> idea after all...
Yes. A custom validator is one solution.
Again, my apologies for sounding rude and dismissive.
Malcolm