Error trying to create a new Mysql table

Question

I am encountering an error while trying to create a table in my database in Mysql.

I read about this error in other occurrences here in stackoverflow, and it seems that it occurs when you try to use a Mysql keyword where it should not be used, but I don’t think this is happening in my code.

Code:

CREATE TABLE tbl_vendas (
    `id` INT UNSIGNED NOT NULL,
    `id_produto` INT UNSIGNED NOT NULL,
    `id_usuario` INT UNSIGNED NOT NULL,
    `email_usuario` VARCHAR(50) NOT NULL,
    `data_compra` DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_produto`) REFERENCES tbl_produtos(`id`),
    FOREIGN KEY (`id_usuario`) REFERENCES tbl_usuario(`id`)
) ENGINE=INNODB;

Error:

#1064 - You have a syntax error in your SQL next to 'CURRENT_DATE,

PRIMARY KEY (id),

FOREIGN KEY (id_produto) REFERENCE' in row 6

Answer 1

The problem is the definition of the date:

`data_compra` DATE DEFAULT CURRENT_DATE

CURRENT_DATE is not supported as default value, as the documentation indicates:

DEFAULT

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".

Link to the documentation

The exception to the rule is the value CURRENT_TIMESTAMP as a default value that can be used in a column of the type TIMESTAMP, as the documentation indicates.