Menu
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?
790
If a column is defined as NOT NULL WITH DEFAULT or if you do not specify NOT NULL, Db2 stores a default value for a column whenever an insert or load does not provide a value for that column. If a column is defined as NOT NULL, Db2 does not supply a default value.
No, it isn't necessary because without adding DEFAULT NULL, it gives NULL value. For example, let's say you haven't added DEFAULT NULL and inserted a record with no value, then the result would display the NULL value as the inserted value.
Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.
If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.
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.
Also, as @Bill Karwin mentions, NULLs behave specially in aggregate functions (COUNT,SUM,AVG etc.): calculating AVG(Temperature) on the above data would give you -14.5, as the NULL row is ignored.
144
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".
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
