Difference between NULL DEFAULT NULL and DEFAULT NULL in MySQL?

Question

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?

If you have just col1 timestamp null, the default null can be omitted (at least according to the documentation). A non-nullable timestamp column has predefined special properties, but they are suppressed when you explicitly make the column nullable. — user743382
– user743382, Commented Sep 10, 2012 at 7:19

informatik01 · Accepted Answer · 2019-05-01 14:59:50Z

12

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).

edited May 1, 2019 at 14:59

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Sep 10, 2012 at 7:05

Thilo

264k107 gold badges527 silver badges674 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amanjot Over a year ago

Can you explain If you only have the default, but make the column reject nulls, then that default cannot be used?

Thilo Over a year ago

The default value must be a an acceptable value for the column. If you say that the column is not-nullable, you cannot put in NULL. So NULL cannot be used for the default value either.

Amanjot Over a year ago

Do you mean to say NULL can only be inserted if the column accepts NULL?

Thilo Over a year ago

Yes, you can only insert NULL into a column that is nullable.

Collectives™ on Stack Overflow

Difference between NULL DEFAULT NULL and DEFAULT NULL in MySQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related