A fairly common requirement for database tables is to have a created and last modified timestamp for each record. This seems to be yet another spot where something fairly straightforward requires you to take the scenic route to implement. Ordinarily, the create table statement would look like:

1
2
3
4
5
6
7

CREATE  TABLE `mydb`.`mytable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `myfield` VARCHAR(255) NOT NULL ,
  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() ,
  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM;

And that’s when MySQL threw me a nice

1

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

Upon digging a little further, I found this little nugget in the documentation:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Well isn’t that just peachy.

The Solution

To overcome this hurdle, I employed the use of a trigger for the created timestamp and decided to leave the modified as is. The create table statement then turned into:

1
2
3
4
5
6
7

CREATE  TABLE `mydb`.`mytable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `myfield` VARCHAR(255) NOT NULL ,
  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() ,
  `created` TIMESTAMP NULL,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM;

And used the following trigger to process the created timestamp –

1
2
3
4
5
6
7

USE `mydb`;
DELIMITER $$
  CREATE TRIGGER trigger_mytable_before_insert BEFORE INSERT ON `mytable`
  FOR EACH ROW BEGIN
      SET NEW.created = NOW();
  END;
$$