3

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

The following configuration

CREATE TABLE test (
  t timestamp not null
);

results in the default CURRENT_TIMESTAMP value, while

CREATE TABLE test (
  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:

CREATE TABLE test (
  i int not null
);
0
4

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, privacy policy and cookie policy

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