CODEWITHSUNDEEP
mysqlsqlmariadb
Ivan P.
Ivan P.

Reputation: 946

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

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?

Upvotes: 0

Views: 87

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

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.

Upvotes: 3

Related Questions