Restrictions on TIMESTAMP Columns with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE Clauses in MySQL Versions Prior to 5.6.5

Historically, in MySQL versions prior to 5.6.5, there was a restriction that limited a table to have only one TIMESTAMP column with either a DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP clause. This limitation extended to INT, BIGINT, and SMALLINT integers back when they were initially introduced in 2008.

This limitation stemmed from legacy implementation concerns that necessitated a specific implementation for the CURRENT_TIMESTAMP functionality.

Error Message and Related Issues

For example, consider the following table definition, which attempts to define two TIMESTAMP columns with the CURRENT_TIMESTAMP value:

CREATE TABLE `foo` (
  `ProductID` INT(10) UNSIGNED NOT NULL,
  `AddedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=INNODB;

This definition would result in the following error:

<pre>Error Code : 1293
Incorrect table definition; there can
be only one TIMESTAMP column with
CURRENT_TIMESTAMP in DEFAULT or ON
UPDATE clause</pre>

This error indicated that the table definition violated the aforementioned restriction.

Removal of Restriction

Despite the technical foundation for this limitation, the MySQL team recognized its inconvenience. Subsequently, in MySQL 5.6.5 (released on April 10th, 2012), the restriction was lifted.

The change log for this update stated:

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions.