What is the consensus on using NOT NULL constraints on all text-based fields in a table?

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.

StackExchange Database Administrators Q#5875, answer score: 6