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

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.