What Does NOT NULL Mean, Really?Filed on Apr 14, 2012 by AnthonyDiSante
There are lots of people asking this question on the internet, and lots of other people purporting to answer it. But the answer is often wrong, or at least, it’s not adressing what the questioner is really asking.
In MySQL, when you define a column as NOT NULL, it doesn’t mean "this column must have a value." It doesn’t mean "this column can’t be blank." What it actually means is that the column will not accept the special MySQL value called NULL. And in most cases, that’s a good thing; in most cases, you should define your columns as NOT NULL, unless you fully understand NULL values and you have a good reason for wanting to allow them.
This can be confusing because NULL seems like it might be the same as 0, or the same as "" (the empty string), but in fact it’s a totally different value. To MySQL, 0, "", and NULL are three different values, even though they all kinda mean "nothing." And the NOT NULL specification on a column has nothing to do with 0 nor with "". You can insert a 0 or a "" into a column defined as NOT NULL; the only thing you can’t insert into it is NULL.
As you can see, NULL is basically one giant pain in the butt. And that’s why NOT NULL is good: it means "I never want to deal with this nonsense, so I forbid NULLs from being stored." Then, you can just deal with your data as it really exists: for strings, they can be empty ("") or not empty according to the needs of your application; integers can be zero or non-zero. And there will never be any pesky NULLs to screw up your database queries.