Archive for August, 2011

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:

Having the created column assign a current timestamp value on insert

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:

ERROR 1293(HY000): Incorrect TABLE definition; there can be ONLY one TIMESTAMPCOLUMNWITHCURRENT_TIMESTAMPINDEFAULTORONUPDATE clause

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 UPDATECURRENT_TIMESTAMP value. However, you can use a DATETIME data type for the created column provided it’s null allowed, like: