DEFAULT CURRENT_TIMESTAMP

User, date Message
Written by kalvaro
4 years ago
Category: Running SQL scripts
442 posts since Thu, 29 Nov 07 		I'm getting a SQL error when I try to add a column. HeidiSQL generates this query:

ALTER TABLE `folder` ADD `updated` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;



And the SQL Server returns this error: Invalid default value for 'updated'

I understand the issue comes from the server, which is pretty old (4.1.13). However, I can't manage to find the exact reference in the MySQL manual. I'd like to know for sure what I can and I cannot do with CURRENT_TIMESTAMP depending on the server version.

Any clue?
Written by ansgar
4 years ago
3973 posts since Fri, 07 Apr 06 		No, I'm nearly sure you have to set the datatype to TIMESTAMP rather than DATETIME for "CURRENT_TIMESTAMP" to be a valid default value.
Written by kalvaro
4 years ago
442 posts since Thu, 29 Nov 07

anse wrote: No, I'm nearly sure you have to set the datatype to TIMESTAMP rather than DATETIME for "CURRENT_TIMESTAMP" to be a valid default value.



You are right, I can add a TIMESTAMP column with such default value even in MySQL 4.1... The manual page seems to be this:

http://dev.mysql.com/doc/refman/4.1/en/timestamp.html

I'll check whether there's already a feature request for HeidiSQL so it hides invalid default values in the drop down list.

Thanks for the tip!
 

Please login to leave a reply, or register at first.