5
votes

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.

1
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) - John Woo
@solomon_wzs The answer is in the error message. Why do you want two fields with the same values? - Devart
@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
There are some restrictions with TIMESTAMP data type. Just follow the documentation. - Devart

1 Answers

9
votes

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;