CODEWITHSUNDEEP
mysql
Neets
Neets

Reputation: 4222

MySQL sets field to current date when value is null

I have a table with two timestamp fields. They are not nullable. The problem is that whenever I insert null into those fields, the current date is automatically saved, instead of throwing an error saying "Column 'first_data_dt' cannot be null", just like it happens when I insert a value into another non-nullable field.

There are no triggers associated to this table.

Does anybody know why this is happening?

EDIT to add table definition:

CREATE TABLE `ui_mytable` (

  `id` int(11) NOT NULL,

  `first_data_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `last_data_dt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I understand now why first_data_dt is updated to the current timestamp anytime I insert null. But what about last_data_dt?

Upvotes: 0

Views: 1787

Answers (1)

Wander Nauta
Wander Nauta

Reputation: 19615

That's what a TIMESTAMP column does:

The TIMESTAMP data type offers automatic initialization and updating to the current date and time (that is, the current timestamp). [...] You can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

Source: MySQL documentation

Maybe you want to use a DATETIME instead?

Upvotes: 2

Related Questions