MariaDB, MySQL - what is wrong with my Create Table Syntax?

Question

I am trying to execute such SQL command to create table for Maria DB:

CREATE TABLE `sh_bom`.`stockmovements` (
  `MOVEMENTID` INT NOT NULL,
  `MOVEMENTDATE` DATETIME NULL,
  `MOVEMENTVALUE` INT NULL,
  `LEFTBEFORE` INT NULL,
  `LEFTAFTER` INT NULL,
  `COMMENTARY` VARCHAR(200) NULL,
  `PARTID_FK` INT NULL,
  PRIMARY KEY (`MOVEMENTID`),
  INDEX `PARTID_FKNAME_idx` (`PARTID_FK` ASC) VISIBLE,
  CONSTRAINT `PARTID_FKNAME`
    FOREIGN KEY (`PARTID_FK`)
    REFERENCES `sh_bom`.`detail` (`Id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

But I get such 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 '
  CONSTRAINT `PARTID_FKNAME`
    FOREIGN KEY (`PARTID_FK`)
    REFERENCES `sh_b' at line 10
SQL Statement:
CREATE TABLE `sh_bom`.`stockmovements` (
  `MOVEMENTID` INT NOT NULL,
  `MOVEMENTDATE` DATETIME NULL,
  `MOVEMENTVALUE` INT NULL,
  `LEFTBEFORE` INT NULL,
  `LEFTAFTER` INT NULL,
  `COMMENTARY` VARCHAR(200) NULL,
  `PARTID_FK` INT NULL,
  PRIMARY KEY (`MOVEMENTID`),
  INDEX `PARTID_FKNAME_idx` (`PARTID_FK` ASC) VISIBLE,
  CONSTRAINT `PARTID_FKNAME`
    FOREIGN KEY (`PARTID_FK`)
    REFERENCES `sh_bom`.`detail` (`Id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

I have checked documentation here: https://mariadb.com/kb/en/foreign-keys/ And I am sure that foreign key is defined correctly:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

What am I missing in this SQL command?

Perhaps your version of MySQL doesn't support VISIBLE and the parser is a little confused. — Gordon Linoff
– Gordon Linoff, Commented Apr 20, 2020 at 11:51

Tim Biegeleisen · Accepted Answer · 2020-04-20 11:56:14Z

3

The visible potential error in your create table statement is that your index definition uses the keyword VISIBLE. Indices in MySQL are by default visible to the optimizer. Starting with MySQL 8+, indices can be made invisible. If you are using a version of MySQL earlier than 8+, your syntax could cause an error.

answered Apr 20, 2020 at 11:56

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MariaDB, MySQL - what is wrong with my Create Table Syntax?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related