sql - MariaDB does not accept specific date as 'default on update' value

Question

Using MariaDB 10.5.8.

The following commands work :

CREATE TABLE t1 (
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
  dt DATETIME DEFAULT '2100-01-01 00:00:000' ON UPDATE CURRENT_TIMESTAMP
);

But the following command does not work :

CREATE TABLE t1 (
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE '2100-01-01 00:00:000'
);

It returns ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`2100-01-01 00:00:000` )' at line 1.

question from:https://stackoverflow.com/questions/66045814/mariadb-does-not-accept-specific-date-as-default-on-update-value

Answer 1

The documentation is really clear on what syntax is allowed:

[ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]

This does not include putting in a custom value.

To be honest, I'm not sure if the designers would have even considered a constant value for such a column. After all, you can just have two columns and check an update if updatedAt <> createdAt.