Why there might be just one TIMESTAMP column with CURRENT_TIMESTAMP in arrears or ON UPDATE clause?
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;
The mistake that results:
Error Code : 1293
Incorrect table definition there can be just one TIMESTAMP column with CURRENT_TIMESTAMP in arrears or ON UPDATE clause
I additionally wondered that very long time ago. I looked a little during my history and i believe this publish: http://lists.mysql.com/internals/34919 signifies the semi-official position of MySQL (before Oracle's intervention ))
In a nutshell:
this limitation stems only in the means by which this feature is presently implemented within the server there aren't any some other reasons because of its existence.
So their explanation is "since it is implemented such as thisInch. Does not seem very scientific. I suppose everything originates from some old code. This really is recommended within the thread above: "carry-over from the time just the first timestamp area was auto-set/update".
Well a treatment for you may be to use it the UpdatedDate area and also have a trigger that updates the AddedDate area using the UpdatedDate value only when AddedDate is null.
1.change data kinds of posts to datetime 2.set trigger for example:
DROP TRIGGER IF EXISTS `update_tablename_trigger`; DELIMITER // CREATE TRIGGER `update_tablename_trigger` BEFORE UPDATE ON `tablename` FOR EACH ROW SET NEW.`column_name` = NOW() // DELIMITER ;
we are able to provide a default value for that timestamp to avert this problem. this publish provide the detail workaround http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/