DEFAULT syntax error

Question

I'd like to add a constraint on a mySQL table for the default value of a datetime column, just like here, but the command solving the issue just doesn't work for me.

I created my table with :

create table `transactions`
      ( `id` int NOT NULL AUTO_INCREMENT, 
        `timeTransaction` DATETIME, 
        `montant` int, 
        `donneur` varchar(50), 
        `receveur` varchar(200), 
        primary key(`id`));

and when I try

ALTER TABLE transactions ADD CONSTRAINT DF_TIME_TRANS DEFAULT GETDATE() FOR timeTransactions;

It always return a syntax error on the DEFAULT :

ERROR 1064 (42000): 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 'DEFAULT GETDATE() FOR timeTransactions' at line 1

I tried defining the constraint during the creation of the table but I still got stuck on the DEFAULT. Any idea why this is happening ?

Don't use SQL Server syntax for MySQL – juergen d Jan 04 '17 at 19:32 — juergen d, Jan 04 '17 at 19:32
In MySQL you use `DEFAULT CURRENT_TIMESTAMP`. – Barmar Jan 04 '17 at 19:34 — Barmar, Jan 04 '17 at 19:34

spencer7593 · Answer 1 · 2017-01-04T19:52:27.893

It's not possible to use the GETDATE() function as a default value.

With TIMESTAMP and DATETIME columns, you can specify DEFAULT CURRENT_TIMESTAMP, either as part of the CREATE TABLE, or with an ALTER TABLE to modify the column definition

ALTER TABLE `transactions`
  CHANGE `timeTransaction` `timeTransaction` DATETIME DEFAULT CURRENT_TIMESTAMP ;

You can also use one of the valid synonyms for CURRENT_TIMESTAMP. (And GETDATE() is not one of the valid synonyms.)

Before MySQL 5.6.x, only TIMESTAMP columns could have DEFAULT CURRENT_TIMESTAMP. With MySQL 5.6, support for that is extended to DATETIME datatype.

Refer to the MySQL Reference Manual for the version of MySQL you are running.

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

DEFAULT syntax error

1 Answers1