How can I add "ON UPDATE CURRENT_TIMESTAMP" on a column at table creation or by alter table withoud defining its default value

Issue

I am using following query to alter a table and add ON UPDATE CURRENT_TIMESTAMP on a column.

ALTER TABLE my_table CHANGE column_name column_name TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP // can I remove this line
ON UPDATE CURRENT_TIMESTAMP;

From the above query, Can I put ON UPDATE CURRENT_TIMESTAMP on a column without defining its default value?

When I try to remove that part, the query will be like:

alter table new_table CHANGE updated_at updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

and it is showing error: alter table new_table CHANGE updated_at updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Error Code: 1067. Invalid default value for ‘updated_at’

Solution

ALTER TABLE t1
  MODIFY COLUMN c1
  TIMESTAMP
  NULL -- the opposite is NOT NULL, which is implicitly set on timestamp columns
  DEFAULT NULL -- no default value for newly-inserted rows
  ON UPDATE CURRENT_TIMESTAMP;

Answered By – Michael – sqlbot

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0