Why there can be only one TIMESTAMP column with CU

Question

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT 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 error that results:

Error Code : 1293

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Answer 1

I also wondered that long time ago. I searched a bit in my history and I think that this post: http://lists.mysql.com/internals/34919 represents the semi-official position of MySQL (before Oracle's intervention ;))

In short:

this limitation stems only from the way in which this feature is currently implemented in the server and there are no other reasons for its existence.

So their explanation is "because it is implemented like this". Doesn't sound very scientific. I guess it all comes from some old code. This is suggested in the thread above: "carry-over from when only the first timestamp field was auto-set/update".

Cheers!

Answer 2

We can give a default value for the timestamp to avoid this problem.

This post gives a detailed workaround: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

create table test_table( 
id integer not null auto_increment primary key, 
stamp_created timestamp default '0000-00-00 00:00:00', 
stamp_updated timestamp default now() on update now() 
);

Note that it is necessary to enter nulls into both columns during "insert":

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5; 
+----+---------------------+---------------------+ 
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)  
mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0  
mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       | 
+----+---------------------+---------------------+ 
|  3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec) 

Answer 3

Indeed an implementation fault.

The native approach in MySQL is to update a creation date yourself ( if you need one ) and have MySQL worry about the timestamp update date ? update date : creation date like so:

CREATE TABLE tracked_data( 
  `data` TEXT,
  `timestamp`   TIMESTAMP,
  `creation_date` TIMESTAMP                                   
) ENGINE=INNODB; 

On creation Insert NULL:

INSERT INTO tracked_data(`data`,`creation_date`) VALUES ('creation..',NULL);

NULL values for timestamp are interperted as CURRENT_TIMESTAMP by default.

In MySQL the first TIMESTAMP column of a table gets both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attribute, if no attributes are given for it. this is why TIMESTAMP column with attributes must come first or you get the error described in this thread.