On Mon, 2006-03-06 at 11:25 +0300, Ivan Sagalaev wrote:
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
