MySQL CURRENT_TIMESTAMP as DEFAULT

Question

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 1

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

Answer 2

I think it's important to note that this limitation is no longer present as of MySQL 5.6.5. The limitation has been removed and some additional functionality has been added. Specifically: multiple TIMESTAMP as well as DATETIME fields can have an automated default which will set the field to the current timestamp/datetime as explained in the following blog post (apparently written by the person who made the fix): http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

More Specifics
Additionally it may be valuable to read up on exactly how to produce default date & time values.
As outlined in the link below you can set both DATETIME & TIMESTAMP data types so the column will have a DEFAULT value of the current date & time of the INSERT operation for a given row. Alternately you can set them ON UPDATE to have the current date & time set in the field on an UPDATE operation.
The Link:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

Here's what both would look like in a CREATE TABLE statement:

CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
,ts2 TIMESTAMP DEFAULT NOW()
);

It's worth pointing out that CURRENT_TIMESTAMP is an alias to NOW().
Additionally setting a TIMESTAMP or DATETIME field to NULL will change the columns DEFAULT to null instead of a date time value, (again see the link for specifics & more details).
Another potentially valuable point is that DATETIME can contain a date range from:
1000-01-01 to 9999-12-31
while TIMESTAMP has a range of:
1970-01-01 00:00:01 to 2038-01-19 03:14:07
For additional comparisons between DATETIME & TIMESTAMP read this MySQL manual page comparing them.

Answer 3

Allow me to quote my own blog post: "MySQL, for whatever reason, only allows one auto-updating timestamp per table. There are a number of ways around this but not all of the ways don’t suck."

The way I recommend is to use a trigger for each timestamp:

CREATE TRIGGER customer_create BEFORE INSERT ON `customer`
FOR EACH ROW SET NEW.created_at = NOW(), NEW.updated_at = NOW();

See the post itself for more details.