Search code examples
mysqlsqlmariadbdatagrip

What is the syntax error in this simple SQL query for creating a table?

I am trying out IntelliJ's DataGrip to do some SQL work on a MariaDB database.

Somehow i cannot execute the query that was automatically created by DataGrip itself....

Can you help me find the error in it ?

create table currencyIndex
(
    id int auto_increment,
    isoCode VARCHAR2(3) not null,
    isoCodeNumeric SMALLINT not null,
    currencyName VARCHAR2(100) not null,
    countryName VARCHAR2(100) not null,
    constraint currencyIndex_pk
        primary key (id)
);

The error is

[42000][1064] (conn=246) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(3) not null,
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(3) not null,
    isoCodeNumeric SMALLINT not null,
    currencyName VARCHAR2(100) ...' at line 4.

I tried to validate the query with an online validator and it seems fine... Any suggestions ?

Solution

  • MariaDB and MySQL do not have a VARCHAR2 type (but Oracle does). Use plain VARCHAR and the error should go away:

    CREATE TABLE currencyIndex (
    id INT AUTO_INCREMENT,
    isoCode VARCHAR(3) NOT NULL,
    isoCodeNumeric SMALLINT NOT NULL,
    currencyName VARCHAR(100) NOT NULL,
    countryName VARCHAR(100) NOT NULL,
    CONSTRAINT currencyIndex_pk PRIMARY KEY (id)
);

    Demo