MariaDB create table throws error on foreign key contraint

Question

Running the following SQL statements (in this order) results in the following error being thrown, I have no idea what's wrong, searched Google for example queries and all seem the same to me.

They work fine when being executed on a MySQL Workbench server but not when I try it on a MariaDb one.

Error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ' CONSTRAINT playerid FOREIGN KEY (playerid) REFERENCES st_player (id) ON DELET' at line 1

SQL statements:

CREATE TABLE IF NOT EXISTS `st_player` (
    `id` VARCHAR(45) NOT NULL, 
    `playerName` VARCHAR(45) NOT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `st_statistic` (
    `criteria` VARCHAR(45) NOT NULL, 
    `subcriteria` VARCHAR(45) NOT NULL, 
    `playerid` VARCHAR(45) NOT NULL, 
    `displayname` VARCHAR(45) NULL,
    `amount` INT NULL, 
    PRIMARY KEY (`criteria`, `subcriteria`, `playerid`), 
    INDEX `playerid_idx` (`playerid` ASC) VISIBLE, 
    CONSTRAINT `playerid` 
        FOREIGN KEY (`playerid`) REFERENCES `st_player` (`id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
);

Answer 1

You are probably running a version of MySQL that does not support invisible indexes feature. VISIBLE is the default behavior anyway, so you could just remove it from your create table statement.

Furthermore, MySQL automatically creates an index on foreign key columns; this means that you don't actually need to explicitly declare that index. I would hence suggest just removing the index declaration.

This codes runs fine:

CREATE TABLE IF NOT EXISTS st_player(
    id VARCHAR(45) NOT NULL,
    playerName VARCHAR(45) NOT NULL, 
    PRIMARY KEY (id)
    -- UNIQUE KEY id(id)
);

CREATE TABLE IF NOT EXISTS st_statistic(
    criteria VARCHAR(45) NOT NULL,
    subcriteria VARCHAR(45) NOT NULL,
    playerid VARCHAR(45) NOT NULL,
    displayname VARCHAR(45) NULL,
    amount INT NULL, 
    PRIMARY KEY (criteria,subcriteria,playerid), 
    -- INDEX playerid_idx(playerid ASC), -- unnecessary (but it it is OK if you uncomment it)
    CONSTRAINT playerid
        FOREIGN KEY (playerid) REFERENCES st_player(id) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
);

Also please note that you do not need a unique index on the primary key column of the first table. A primary key is unique already. I commented that too.