Change the on update CURRENT_TIMESTAMP constraint
Problem:
You want to remove the "on update CURRENT_TIMESTAMP" constraint at a specific column and alter this constraint to another column in
your table. You receive the following error message with a single ALTER command:
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
The red highlited line shows the constraint you might want to be removed.
mysql> desc messages;
+-------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| board_id | int(11) | | | 1 | |
| par_id | int(11) | | | 0 | |
| msg_type | int(11) | | | 1 | |
| status | smallint(6) | | | 1 | |
| uid | int(11) | | | 1 | |
| title | varchar(255) | YES | | NULL | |
| mtext | text | YES | | NULL | |
| lang | varchar(8) | YES | | de | |
| ts_cre | timestamp | YES | | CURRENT_TIMESTAMP | |
| ts_mod | timestamp | YES | | 0000-00-00 00:00:00 | |
| service_key | int(8) | | | 0 | |
| perm | int(11) | | | 0 | |
| ord | int(11) | YES | | 0 | |
+-------------+--------------+------+-----+---------------------+----------------+
14 rows in set (0.00 sec)
ALTER TABLE messages ADD ts_mod_new timestamp;
ALTER TABLE messages ADD ts_cre_new timestamp;
UPDATE messages SET ts_mod_new = ts_mod;
UPDATE messages SET ts_cre_new = ts_cre;
ALTER TABLE messages DROP ts_mod;
ALTER TABLE messages DROP ts_cre;
ALTER TABLE messages ADD ts_mod timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
UPDATE messages SET ts_mod = ts_mod_new;
ALTER TABLE messages DROP ts_mod_new;
ALTER TABLE messages ADD ts_cre timestamp NOT NULL default '0000-00-00 00:00:00';
UPDATE messages SET ts_cre = ts_cre_new;
ALTER TABLE messages DROP ts_cre_new;
Save these lines as fix_tstamp.sql
$ mysql -D dbname < fix_tstamp.sql
mysql> desc messages;
+-------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| board_id | int(11) | | | 1 | |
| par_id | int(11) | | | 0 | |
| msg_type | int(11) | | | 1 | |
| status | smallint(6) | | | 1 | |
| uid | int(11) | | | 1 | |
| title | varchar(255) | YES | | NULL | |
| mtext | text | YES | | NULL | |
| lang | varchar(8) | YES | | de | |
| service_key | int(8) | | | 0 | |
| perm | int(11) | | | 0 | |
| ord | int(11) | YES | | 0 | |
| ts_mod | timestamp | YES | | CURRENT_TIMESTAMP | |
| ts_cre | timestamp | YES | | 0000-00-00 00:00:00 | |
+-------------+--------------+------+-----+---------------------+----------------+
14 rows in set (0.01 sec)
mysql> select ts_cre, ts_mod from messages;
+---------------------+---------------------+
| ts_cre | ts_mod |
+---------------------+---------------------+
| 2006-05-16 13:17:10 | 2006-05-16 13:17:59 |
| 2006-05-16 13:16:54 | 2006-05-16 13:17:59 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
iC Home > Know-how >
MySQL Database >
ALTER TABLE ... > Change Constraint