Mysql : Insert null timestamp into NOT NULL column

Question

Mysql version is 5.1.73,there can be only one timestamp column with current_timestamp in deafault or on update clause,so I tried to create table:

create table `temp`( 
  `id` INTEGER NOT NULL auto_increment PRIMARY KEY, 
  `created_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_updated_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

And after I executed this sql:

insert into `temp`(id,created_date, last_updated_date) values(1,null, null);

I got this document:

1   2017-02-10 18:17:16 2017-02-10 18:18:13

Here is my question:

I set created_date NOT NULL,why did the insert sql work well?Should it return Error: Column 'xx' cannot be null？
Why did created_date column get a value and it is not the default value?

Thanks.

NULL !=0; NULL <> 0; NULL=??????????? Your default is a value NULL has no value. — Namphibian
– Namphibian, Commented Feb 10, 2017 at 11:35

Imran Zahoor · Accepted Answer · 2017-02-10 11:29:16Z

1

That's how the MySql's TIMESTAMP work, if you want to get an error on NULL the way to go is to use DATETIME instead.

So if you create the following table

create table `temp`( 
  `id` INTEGER NOT NULL auto_increment PRIMARY KEY, 
  `created_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_updated_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

And then execute the below query you'll get the error.

insert into `temp`(id,created_date, last_updated_date) values(1,null, null);

Now you'll start getting this error #1048 - Column 'created_date' cannot be null.

answered Feb 10, 2017 at 11:29

Imran Zahoor

2,8371 gold badge34 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Mysql : Insert null timestamp into NOT NULL column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related