mysql - 使用外键创建表时如何修复错误 1064？

Question

MySql 工作台告诉我，与我的 MariaDB 服务器版本相对应的 SQL 语法存在错误，但我不知道原因。

我已经看过其他问题，但似乎没有一个有帮助，所以我现在问自己的问题...我真的不知道我的代码出了什么问题，我也不知道问题出在哪里我的语法是。我尝试设置一些逗号或重命名列等，并将模型中的 SQL 版本更改为我与 xampp 一起使用的 sql 版本。但是，更改版本后我仍然收到该错误......

创建表的sql是(我已经把注释去掉了):

CREATE SCHEMA IF NOT EXISTS `testDB` DEFAULT CHARACTER SET utf8 ;
USE `testDB` ;

CREATE TABLE IF NOT EXISTS `testDB`.`person` (
  `personID` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(45) NULL,
  `lastname` VARCHAR(45) NULL,
  PRIMARY KEY (`personID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `testDB`.`comment` (
  `commentID` INT NOT NULL AUTO_INCREMENT,
  `comment` VARCHAR(45) NOT NULL,
  `person_personID` INT NOT NULL,
  PRIMARY KEY (`commentID`),
  INDEX `fk_comment_person_idx` (`person_personID` ASC) VISIBLE,
  CONSTRAINT `fk_comment_person`
    FOREIGN KEY (`person_personID`)
    REFERENCES `testDB`.`person` (`personID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

我得到的错误是:

Executing SQL script in server
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 `fk_comment_person`
    FOREIGN KEY (`person_personID`)
    REFERE' at line 9
SQL Code:
        -- -----------------------------------------------------
        -- Table `testDB`.`comment`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `testDB`.`comment` (
          `commentID` INT NOT NULL AUTO_INCREMENT,
          `comment` VARCHAR(45) NOT NULL,
          `person_personID` INT NOT NULL,
          PRIMARY KEY (`commentID`),
          INDEX `fk_comment_person_idx` (`person_personID` ASC) VISIBLE,
          CONSTRAINT `fk_comment_person`
            FOREIGN KEY (`person_personID`)
            REFERENCES `testDB`.`person` (`personID`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 6 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Answer 1

删除语句中的“VISIBLE”一词:

CREATE SCHEMA IF NOT EXISTS `testDB` DEFAULT CHARACTER SET utf8 ;
USE `testDB` ;

CREATE TABLE IF NOT EXISTS `testDB`.`person` (
  `personID` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(45) NULL,
  `lastname` VARCHAR(45) NULL,
  PRIMARY KEY (`personID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `testDB`.`comment` (
  `commentID` INT NOT NULL AUTO_INCREMENT,
  `comment` VARCHAR(45) NOT NULL,
  `person_personID` INT NOT NULL,
  PRIMARY KEY (`commentID`),
  INDEX `fk_comment_person_idx` (`person_personID` ASC),
  CONSTRAINT `fk_comment_person`
    FOREIGN KEY (`person_personID`)
    REFERENCES `testDB`.`person` (`personID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

因为MariaDB中还没有实现INVISIBLE选项。该选项在ORACLE中实现