I want a non-nullable timestamp column where the inserter is required to specify what the value should be.

The following configuration

  t timestamp not null

results in the default CURRENT_TIMESTAMP value, while

  t timestamp not null default null

results in

ERROR 1067 (42000): Invalid default value for 't'

I want the same behavior that, for example, the int column below has:

  i int not null

1 Answer 1


I think that the only way is setting the server variable explicit_defaults_for_timestamp to ON (default is OFF).

This will disable the non-standard behaviour of timestamp columns regarding NULL values:

If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

  • It is not possible to assign a TIMESTAMP column a value of NULL to set it to the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().

  • ...

  • TIMESTAMP columns declared with the NOT NULL attribute do not permit NULL values. For inserts that specify NULL for such a column, the result is either an error for a single-row insert or if strict SQL mode is enabled, or '0000-00-00 00:00:00' is inserted for multiple-row inserts with strict SQL mode disabled. In no case does assigning the column a value of NULL set it to the current timestamp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.