What's the point of "NOT NULL DEFAULT '' "?

Question

I've seen this on a lot of fields on a DB from a project I've been working on, where a column will be defined not null, but will have an empty string as the default. what's the point of doing this? If you will allow an empty string, why not just allow a field to be null?

Answer 1

Because sometimes, on some languages, a "select" query would give you the string NULL, and you would have to check if it's a real string with content "NULL" or it is really null.

With "" it's easiest (IMHO)

Answer 2

null and "" are not the same thing, so there is no contradiction here.

What the semantic meaning of null/"" is depends on the individual and is often a "religious" issue. To some people in some schemas, of course, they may be the same, but they don't have to be. For example, a "" might mean "I explicitly asked the user for input, and they chose to enter nothing" while a null might mean "I never even asked for input".

Answer 3

We do not allow nulls because programmatically accessing a field from a datatable with a DBnull value causes an exception, while an empty string has a value and therefore does not error.

Answer 4

There is a difference between a null value and an empty string - at least in SQL.

SELECT LENGTH('tata');
4

SELECT LENGTH(NULL);
NULL

SELECT LENGTH('tata')-LENGTH('');
4

SELECT LENGTH('tata')-LENGTH(NULL);
NULL

Answer 5

Most programming languages that interact with a database don't natively support a NULL that works the way it does in a database. For example in C#, null <> DbNull.Value. In VB most data types couldn't cope with null semantics, e.g. you couldn't store a null in a Date, or an Int, etc. By reducing the number of nulls the client programs see, the less headaches there are. In the case of VB, the closes thing that means null for strings is Empty, which is something closer to not-initialized, not quite the same meaning as NULL in the sense of a "not known"

Answer 6

NULLs have special behavior: comparing anything with a NULL gives you back a NULL, which is something else than false or 0. It means "unknown".

For example, take this table:

 user_id | gender
------------------
 1       | NULL
 2       | 'M'
 3       | 'F'
 4       | 'F'

SELECT * FROM mytable WHERE gender = 'M' will return 1 row, as expected

SELECT * FROM mytable WHERE gender != 'M' will return 2 rows, NOT 3 rows.

SELECT * FROM mytable WHERE gender != 'M' OR gender IS NULL will return the expected 3 rows.

---

Edit: For some applications, using 0 (or, God forbid, another "magic number") instead of NULL is not even advisable (units or exact values are not relevant in this example):

 Date       | Temperature 
--------------------------
 2010-01-01 | 10          
 2010-01-02 | 4
 2010-01-03 | 0
 2010-01-04 | -22
 2010-01-05 | -45
 2010-01-06 | NULL
 2010-01-07 | -34

Here, the NULL on Jan 6th means "value unknown" - maybe because the temperature was so low that the thermometer probe stopped responding. However, it's a completely different meaning than Jan 3rd, when the temperature was 0, that is, 0 degrees.

Answer 7

In addition to what @Piskvor mentioned, this practice is also there so as to prevent some potential NullPointerExceptions in the application logic when populating beans/objects etc. after reading from DB