Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying.
I'm curious what the feelings are with using something like NOT NULL DEFAULT '', which would replace the need of the coalesce() function when retrieving/filtering records. On the other hand instead of coalesce, you'd have to use CASE statements or, if you still wanted to use coalesce, you'd do something like coalesce(nullif(field_foo,''), field_bar)
Semantically that data IS NULL, setting it to something else is awkward at best. We can always assume NULL is NULL, we know what that is. if you use '' then all of a sudden you have to keep track of what you're setting as your new NULL. Just use the construct of the system where possible.
Using a value for a null has problems as well; a query will never match a null to anything; it's not a value so no range of options will ever match it; the exception is the IS NULL condition, which has special semantic meaning as NULL has special semantic meaning.
In addition you're dubiously exploiting the database's NOT NULL constraint. That constraint is meant to mean that column always has data. Your data technically fulfills that constraint, but it does not semantically.
The only reason I can see for setting NOT NULL and using a homebrewed "NULL" value is when an application is inserting it's own data and it has it's own value for NULL. For instance PHP's NULL when stored to a database column will appear as a value, not a SQL NULL. This can be avoided with proper programming in the application however, and I would only consider the "fake NULL" solution if I had no control over the application that was inserting "fake nulls"-- at that point the battle is lost, and your solution becomes the most applicable.
I think the main reasoning behind specifying NOT NULL is because allowing NULL on a column basically adds 1 byte extra to its size.
So for example, you can have a big integer which is 8 byte and the column allows NULL, then it would be 9 bytes per row stored on disk.
If you feel that its important for the way you store your data to have your column have a NULL, then by all means use it. It useful if something like '' can count as "something" and you prefer to use NULL which is "nothing".
NULL for databases, so long as it doesn't mess with the logic. However a NULL value is a special meaning, which is that the field has no value. This is problematic when you have many people that write to many different databases, and you're having to always check if fields have NULLs vs making it a company policy to set default not null on the table.
– vol7ron
Sep 16 '11 at 23:14
NULL is important and good implemented in databases. E.g. if a birthdate is not known for a user, the value should be NULL, not a "fake date". But it is not usable on all columns, just use it where it is a good design. NULL is worse implemented in popular programming languages, where almost everything may be NULL. Functional programming language usually has a better implementation with an Option-type that can be Some(value) or None.
– Jonas
Sep 17 '11 at 8:29
NULL is, what I haven't seen is any good implementation of it. Most of my data are either dates, or strings (not numbers), for those cases I'm trying to see if there's any reason I should not set defaults.
– vol7ron
Sep 17 '11 at 17:09
NAME and set it to "" that row has a "fake name", it would be better to have it as NULL if the name isn't set.
– Jonas
Sep 17 '11 at 17:22
WHERE name IS NOT NULL versus WHERE name != '' I understand that NULL isn't set, but you it's much harder to change something back to NULL then it is to set it as an empty string
– vol7ron
Sep 17 '11 at 17:42
