Menu
Recently I faced a new problem in MySQL. I was about to create a new table with
col1 TIMESTAMP DEFAULT NULL
(i.e. the column having the default NULL value), but on creation that gave me an error:
Invalid default value for column
But when I tried col1 TIMESTAMP NULL DEFAULT NULL, that table got created.
I want to know what is the difference between the above two syntaxes. I also faced this issue earlier too in some insert NULL values in column.
Can any one explain the cause of this problem, like is it a version specific issue or something else with MySQL?
704
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.
"Standard" SQL specifies the following rules: For each row in table, a column can contain a value or NULL which indicates "no value." If a column is declared NOT NULL, it must always contain a non-NULL value; NULL is not allowed.
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
There's no difference. The default value of any reference type is null .
The first NULL says that the column is nullable, i.e. accepts NULL. The second NULL (after DEFAULT) is the default value.
If you only have the default, but make the column reject nulls, then that default cannot be used.
(I was under the impression, though, that if you specify neither NULL nor NOT NULL that the column was nullable, so what you see is a bit confusing).
116
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
