Clarifying - NULL vs ""

[mat]

If I'm creating a database table with fields which may sometimes have a value and other times not, should I be ticking the NULL box ? (which makes the default NULL) or do I not need to?

[Hawkmoon]

Go with NULL (allows for diversity) and just know that you can still compare with '' if you want to find a "blank" entry.

ie SELECT * FROM table WHERE nullColumn = '';

if you want to test whether or not anything exist use IS NULL or IS NOT NULL

ie SELECT * FROM table WHERE nullColumn IS NULL;

This will return a 0/1 boolean

Here is a link with more info:
http://www.mysql.com/doc/en/Working_with_NULL.html

[raf]

If I'm creating a database table with fields which may sometimes have a value and other times not, should I be ticking the NULL box ? (which makes the default NULL) or do I not need to?:confused:
Null vs Not Null ---> if you want to allow cells to be empty/system missing, then you should not pecify anything. Like

ALTER TABLE `tester` ADD `tesvar` TINYINT UNSIGNED
But thathas nothing to do with your defaultvalue !!

If you wan't to set a default then you can use
ALTER TABLE `tester` ADD `tesvar2` TINYINT UNSIGNED DEFAULT "99"

If you don't want to allow empty cells, so if you set the variable to Not Null like

ALTER TABLE `tester` ADD `testvar3` TINYINT UNSIGNED NOT NULL

But also here, you could set a default like
ALTER TABLE `tester` ADD `testvar3` TINYINT UNSIGNED DEFAULT "44" NOT NULL

then 44 will be inserted if no value is specified for this column
If you specify a default yourself, then there is no difference between having a Null or Not Null variable.

There is only a differende when you don't specify a default. In that case, the first column (testvar) will remain Null.
The third column (testvar3) will get a 0 or '' as value --> if it's a numerical variabletype then it will get a 0, if it'sa stringtype, then it will get a '' (empty string)

This is an important difference, because if you for instance would compute the average between 12,20,13, Null, Null then you would get 15. The average of 12,20,13,0,0 is 9 --> null values are ignored in mathematical functions


One of dr Codds 12 ruke for an ideal relational db is that is should allow null's for all variables, so you need to have a good reason to set a column to Not Null or to set a defaultvalue...

Null vs '' --> Null is a real empty cell, a system missing. '' is a real value. an empty string