If I create a table

CREATE TABLE t1 ( id BIGINT UNSIGNED NOT NULL, createDate TIMESTAMP(3) NOT NULL, updateDate TIMESTAMP(3) NULL DEFAULT NULL, closeDate TIMESTAMP(3) NULL DEFAULT NULL, PRIMARY KEY (id)
) ENGINE=INNODB;

createDate column is created as

`createDate` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

1) What is the logic behind this? Why MySQL adds the default value? And why it adds ON UPDATE CURRENT_TIMESTAMP(3)?

2) Is it possible to create a column timestamp(3) NOT NULL without the default value?

EDIT1:

I have an impression that id column, for example, does not have a default value, because if I do

insert t1 updateDate values (now());

I get

ERROR 1364 (HY000): Field 'id' doesn't have a default value

so the default value is not added automatically to a column of INT type.

1 Answer

First of all: Every column has a default value. In many cases this default value is NULL, but if declared NOT NULL it must obviously be something else.

With the TIMESTAMP data type, the default value is CURRENT_TIMESTAMP(), but of course this can be overwritten:

createDate TIMESTAMP(3) NOT NULL DEFAULT '0000-01-01 00:00:00'

will e.g. set a default value of 0.0 A.D.

4

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy