CODEWITHSUNDEEP
mysqlsqlindexingmariadbcreate-table
branhillsdesign
branhillsdesign

Reputation: 23

SQL Error 1064: You have an error in your SQL syntax

I cannot, for the life of me, figure out what's wrong. I created the ERD and relationships in MySQL workbench and it spit this code out. I forward engineered it to my dbms and i get this error.

Executing SQL script in server
ERROR: Error 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 ')
ENGINE = InnoDB' at line 10
SQL Code:
        -- -----------------------------------------------------
        -- Table `deadiade_golfbag`.`users`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `deadiade_golfbag`.`users` (
          `id` INT NOT NULL AUTO_INCREMENT,
          `first_name` VARCHAR(150) NOT NULL,
          `last_name` VARCHAR(250) NULL,
          `professional` BINARY(1) NOT NULL DEFAULT 0,
          PRIMARY KEY (`id`, `professional`),
          UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
        ENGINE = InnoDB

SQL script execution finished: statements: 5 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Upvotes: 0

Views: 2582

Answers (1)

GMB
GMB

Reputation: 222682

MariaDB does not support invisible indexes - unlike MySQL, which added this feature in version 8.0.

The parser of the MariaDB 10.5 seems to allow VISIBLE (while it fails on INVISIBLE), but I suspect that it basically ignores it. In earlier versions, it just fails on VISIBLE.

VISIBLE is the default setting anyway, so you can just leave that keyword apart (as well as ASC on the indexed column, which also is the default):

CREATE TABLE IF NOT EXISTS `deadiade_golfbag`.`users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(150) NOT NULL,
    `last_name` VARCHAR(250) NULL,
    `professional` BINARY(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`, `professional`),
    UNIQUE INDEX `id_UNIQUE` (`id`) 
) ENGINE = InnoDB

Note that it does not make sense to have a primary key on two columns and and a unique index on one of these two: the unique key is more restrictive that the primary key.

Upvotes: 2

Related Questions