MacLochlainns Weblog

Somebody asked how to work around an error message they got after converting one of their who-audit columns to a TIMESTAMP column. A TIMESTAMP column has a DEFAULT or ON UPDATE current timestamp. They wanted to have two TIMESTAMP columns in the same table, with the intention of:

1. Having the created column assign a current timestamp value on insert
2. Having the updated column assign a current timestamp value on insert and update

This is a sample table with two of the traditional four who-audit columns:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    TIMESTAMP NOT NULL 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

When they tried to create the table they got the following error:

ERROR 1293 (HY000): Incorrect TABLE definition; there can be ONLY one TIMESTAMP COLUMN WITH CURRENT_TIMESTAMP IN DEFAULT OR ON UPDATE clause

While you can define a table with two columns that have a TIMESTAMP data type, you can’t define a table with two TIMESTAMP columns when one holds a DEFAULT or ON UPDATE CURRENT_TIMESTAMP value. However, you can use a DATETIME data type for the created column provided it’s null allowed, like:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    DATETIME 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

You can then define an on insert database trigger that fires when you create a new row, like this:

CREATE TRIGGER sample_t
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
  SET NEW.created = CURRENT_TIMESTAMP();
END;
$$

An INSERT statement would look like this:

INSERT INTO sample VALUES (NULL, NULL, NULL);

You would see the following if you query the table:

+-----------+---------------------+---------------------+
| sample_id | created             | updated             |
+-----------+---------------------+---------------------+
|         1 | 2011-08-11 00:26:21 | 2011-08-11 00:26:21 |
+-----------+---------------------+---------------------+

Hope this helps other too.