In MySQL, how can I have non-nullable timestamp column without a default value?

Question

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
);

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.