Search code examples
mariadbmysql-workbenchmariasql

why is there error 1064 in my script when i executed it

MySQL

Error Code: 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 7

CREATE TABLE IF NOT EXISTS `game_review`.`users` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `email_address` VARCHAR(45) NOT NULL,
  `password` VARCHAR(6) NOT NULL,
  `username` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) VISIBLE)
ENGINE = InnoDB;

I expected this to execute properly since I forward engineered it with my ER diagram that I created, but it gives me the error message above.

Solution

  • MariaDB does not support invisible indices, so the VISIBLE and INVISIBLE keywords are not used. Indices are already visible to the optimizer by default, so you could just use:

    UNIQUE INDEX user_id_UNIQUE (user_id)

    But, a primary key column should already be unique, so you can probably just not even include the unique index.

    Side note: MySQL 8+ does support invisible indices, see here, but your MariaDB version seems to not support them.