MySQL CURRENT_TIMESTAMP as DEFAULT
mysql
Solution
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
Problem
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?