What is the reason for Mysql error code 1293

Question

Here is the message of mysql error 1293:

SQL Error (1293): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

what is the reason for mysql only allows one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause per table.

i'm not voting to close as there are others might have an idea about this, but [there is already an existing question like this one (*click here*)](http://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla) — John Woo, Nov 02 '12 at 07:32
@solomon_wzs The answer is in the error message. Why do you want two fields with the same values? — Devart, Nov 02 '12 at 08:45
@Devart I want to create a table like this `create table mytable{id int auto_increment not null primary key, create_time timestamp not null default CURRENT_TIMESTAMP, last_update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP}` — solomon_wzs, Nov 04 '12 at 15:42
There are some restrictions with TIMESTAMP data type. Just follow the documentation. — Devart, Nov 05 '12 at 07:09

score 9 · Accepted Answer · answered Nov 02 '12 at 09:59

Only one TIMESTAMP field can default to "now" I should say first of all, if you are trying to define more than one MySQL TIMESTAMP fields using CURRENT_TIMESTAMP or "default now", unfortunately that is bad, you can't do it in MySQL I just got this MySQL TIMESTAMP error when trying to create a table like this:

create table users (
    id int unsigned auto_increment not null primary key,
    username varchar(50) not null unique,
    password varchar(40) not null,
    email_address varchar(128) not null unique,
    email_sent timestamp not null,
    last_login timestamp not null default now()
    ) ENGINE = InnoDB;

When I first solved this problem I thought MySQL required the "CURRENT_TIMESTAMP (default now)" field to be declared before any other TIMESTAMP fields, so I solved my problem like this:

create table users (
   id int unsigned auto_increment not null primary key,
   username varchar(50) not null unique,
   password varchar(40) not null,
   email_address varchar(128) not null unique,
   last_login timestamp not null default now(),
   email_sent timestamp not null
 ) ENGINE = InnoDB;

The error message is actually misleading - it implies that only one field can have a set default/update value - but in reality, it's only the first timestamp field that can have a default/update value. — Elie, Jun 21 '13 at 02:20

What is the reason for Mysql error code 1293

1 Answers1