Error with default value for datetime field in MySQL

Question

Error with default value for datetime field in MySQL

Navigation

#1 by (7 votes)

4

I had a problem trying to run my database script on the server. The error already gives in the first table that the script generates:

CREATE TABLE IF NOT EXISTS 'categoria' (
'cd_categoria' int(255) NOT NULL,
  'titulo' varchar(100) NOT NULL,
  'cd_status' int(1) NOT NULL,
  'dh_timestamp' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'dh_alteracao' datetime NOT NULL
) ;

The error that happens is as follows:

Error Code: 1067. Invalid default value for 'dh_timestamp'

I did not understand what happened because I need my script to store in the dh_timestamp column the current date on which there is a record of something. How can I fix this?

mysql sql

asked by anonymous 23.06.2016 / 17:09

1 answer

Oracle SQL result ordering How to get a text from a QLineEdit in Qt?

score 7 · Accepted Answer

It is not possible to set a default value as now() or CURRENT_TIMESTAMP (which are synonyms) for columns of datetime type in versions prior to MySQL 5.6, in which case the simplest solution is to change the type of datetime for timestamp . If the exchange of type is not viable another solution as said by sergio in comments is to create a trigger to set the default value, this answer from SOen shows you how to do it.