MYSQL | #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP

Author - Ishpreet Kaur

Suppose we want to have columns created_date and last_modified_time with default timestamp in any table in MySQL/Phpmyadmin.

Let us take an example of Students table.

Create table Students using following query :

CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY ,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
class varchar(20) NOT NULL,
address varchar(300) NOT NULL,
email varchar(50) NOT NULL,
created_date TIMESTAMP NOT NULL,
last_modified_time TIMESTAMP NOT NULL
);

created_date column is to know when record was first created and last_modified_time column is to know when record was last updated.

If requirement is to assign default values to these columns and insert values without using any programming language, we normally write query as below :

CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY ,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
class varchar(20) NOT NULL,
address varchar(300) NOT NULL,
email varchar(50) NOT NULL,
created_date TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP,
last_modified_time TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

For created_date, we assigned default value CURRENT_TIMESTAMP so that whenever new record is inserted, then created_date column is initialized automatically with value of datetime.

TIMESTAMP format is ‘YYYY-MM-DD h:i:sa’. We assigned DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP to last_modified column so that its value is automatically initialized with default time whenever new record is created or updation is made to the column.

Similarly we can use DEFAULT NOW() to assign default values to columns instead of DEFAULT CURRENT_TIMESTAMP.

But the above method (create statement) displays the following error :

#1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

This error means that MySQL does not allow DEFAULT CURRENT_TIMESTAMP or DEFAULTNOW() more than once in a table. This default initialization of timestamp can be used on only one column.

But sometimes requirement arises when we need created_date to have value of when was the first record created, last_modified_time and when was the record updated without passing our own values or using coding.

To Solve this issue, create table as follows :

CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY ,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
class varchar(20) NOT NULL,
address varchar(300) NOT NULL,
email varchar(50) NOT NULL,
created_date TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
last_modified_time TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

In place of NOW(), we can also use CURRENT_TIMESTAMP .

To have automatic initialization of current datetime in created_date column, pass null in insert statement as shown below :

INSERT INTO `students`(`first_name`, `last_name`, `class`, `address`, `email`, `created_date`) VALUES ('John','Doe', 'XI', 'Amritsar', 'john.doe@mail.com',null);
INSERT INTO `students`(`first_name`, `last_name`, `class`, `address`, `email`, `created_date`) VALUES ('Abc','Def', 'XIX', 'ABCDEF', 'abc.def@mail.com',null);

If we don’t pass null, then created_date column will store value ‘0000-00-00 00:00:00’ for given record during insertion :

UPDATE `students` SET last_name='Smith' WHERE id=3

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *