Bart Platak Bart Platak - 6 months ago 27
MySQL Question

MySQL CURRENT_TIMESTAMP as DEFAULT

While creating a table I am getting the following error:

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


The problem is that I don't actually have two columns
TIMESTAMP
with
CURRENT_TIMESTAMP
as default, neither I am using
ON UPDATE
clause.

The DDL query I'm trying to execute is

CREATE TABLE user(
/* Basic Information */
id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(255) NOT NULL UNIQUE,
password CHAR(40) NOT NULL,

/* System status information */
active BOOL NOT NULL DEFAULT FALSE,
validated BOOL NOT NULL DEFAULT FALSE,
date_validated TIMESTAMP,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

/* Index */
PRIMARY KEY (id)
) Engine=InnoDB;


What's causing the issue?

Answer

You can use two timestamp in one table. For default, use DEFAULT field first and then the rest timestamp fields.

Below query should work.

CREATE TABLE myTable 
(
 id INT,
 date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 date_validated TIMESTAMP
);

Demo at sqlfiddle

Comments