Table column custom default question?

User, date Message
Written by davidk
4 months ago
Category: General
3 posts since Tue, 29 Jan 13 		I see if I use the "Custom" field of the default, that it automatically wraps it in single carrots '' to dictate a string. I'd like to default it to the UTC_TIMESTAMP which can syntactically be done in MySQL, but I can't get it to work in the editor? Is there a proper way for me to be doing this through HeidiSQL?

Thanks!
Written by ansgar
4 months ago
4024 posts since Fri, 07 Apr 06 		Special values such as NULL or CURRENT_TIMESTAMP can only be applied when I give these their own radio button in that popup editor. Didn't knew about UTC_TIMESTAMP yet, so HeidiSQL does not provide it in the table designer yet. Have some documentation link for me?
Written by kalvaro
4 months ago
456 posts since Thu, 29 Nov 07 		I had to read your question three times but I guess that you are talking about the table editor and you want to set the default value to "UTC_TIMESTAMP".

Are you using MySQL or some of its forks such as MariaDB? In regular MySQL is does not seem possible:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.


http://dev.mysql.com/doc/refman/5.5/en/create-table.html

It does indeed trigger a syntax error:

CREATE TABLE foo (
foo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
date_created TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
PRIMARY KEY (foo_id)
);
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP,
PRIMARY KEY (foo_id)
)' at line 3 */



... while CURRENT_TIMESTAMP doesn't:

CREATE TABLE foo (
foo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (foo_id)
);



HeidiSQL is just a GUI client for MySQL Server. I won't allow you to do stuff that's not supported by the server.
 

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