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

Question

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.

What is `VISIBLE`? That doesn't seem to be a keyword in the [MariaDB `CREATE TABLE` documentation](https://mariadb.com/kb/en/library/create-table/). — David Maze, Dec 21 '18 at 02:33
I am actually not sure because i only started with this for 1 month so i just press forward engineering and it gives me this result. but still thank you very much for editing my question. Very appreciated — kovan chew, Dec 21 '18 at 02:40
Remove the `UNIQUE INDEX` line; it's redundant because `PRIMARY KEY` is a unique index. — Rick James, Dec 21 '18 at 03:00

score 1 · Accepted Answer · answered Dec 21 '18 at 02:34

1

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.

answered Dec 21 '18 at 02:34

Tim Biegeleisen

502,043
27
286
360

Thank you very much. It works when i add this UNIQUE INDEX user_id_UNIQUE (user_id) but may i ask how about the ASC? My MariaDB version is 10.1.36-MariaDB – kovan chew Dec 21 '18 at 02:42
Indices on MySQL/MariaDB should be ascending by default, so you may just omit the `ASC` keyword. – Tim Biegeleisen Dec 21 '18 at 02:49
Oh sorry sir, i still have another problem. There is an error for my other table. This is the 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 ' INDEX `user_id_idx` (`iduser` ASC) VISIBLE, INDEX `game_id_idx` (`idgame` A' at line 7 – kovan chew Dec 21 '18 at 02:57
Use `INDEX user_id_idx (iduser), INDEX game_id_idx (idgame)` – Tim Biegeleisen Dec 21 '18 at 02:58

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

1 Answers1