CODEWITHSUNDEEP
phpmysqlsql
qwertymk
qwertymk

Reputation: 35284

mysql column for insert and different one for update

I'm trying to make a table that has two timestamps columns, one will be for when a row is created and the other for when the row is updated. Here's what I tried so far:

CREATE TABLE `tmp` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `data` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `created` timestamp NOT NULL ,
   `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

But I'm getting this error:

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

Upvotes: 0

Views: 118

Answers (3)

Jeremy Smyth
Jeremy Smyth

Reputation: 23503

You can do this in MySQL 5.6 (available at the time of writing as release candidate but not yet production-ready).

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.... For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both

Upvotes: 1

juergen d
juergen d

Reputation: 204794

You could write a trigger to add the created timestamp on inserts seperately

delimiter |
CREATE TRIGGER add_created_ts BEFORE INSERT on `tmp`
FOR EACH ROW
BEGIN
   SET NEW.created = current_timestamp;
END
|
delimiter ;

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

This is a MYSQL constraint, You can have only one column whose default value will be the systime.

This question can also be referred How to add "ON update current timestamp" to existing table column

Upvotes: 1

Related Questions