CODEWITHSUNDEEP
mysql
Ghodot
Ghodot

Reputation: 41

DEFAULT syntax error

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 ?

Upvotes: 4

Views: 5637

Answers (1)

spencer7593
spencer7593

Reputation: 108400

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

Upvotes: 2

Related Questions