使用MariaDB 10.4.21创建表时外键约束报1064语法错误求助

错误提示 #

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 ' CONSTRAINT fk_artwork_artist FOREIGN KEY (artist_id) REFERENCE...' at line 10

环境与原SQL语句 #

使用MariaDB 10.4.21版本，执行的CREATE TABLE语句如下：

CREATE TABLE IF NOT EXISTS `art_gallery`.`artwork` (
  `artwork_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `artyear` INT(4) NOT NULL,
  `period` VARCHAR(25) NULL,
  `arttype` VARCHAR(20) NULL,
  `artfile` VARCHAR(25) NOT NULL,
  `artist_id` INT NOT NULL,
  PRIMARY KEY (`artwork_id`),
  INDEX `fk_artwork_artist_idx` (`artist_id` ASC) VISIBLE,
  CONSTRAINT `fk_artwork_artist`
    FOREIGN KEY (`artist_id`)
    REFERENCES `art_gallery`.`artist` (`artist_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

问题原因 #

MariaDB 10.4版本不支持VISIBLE/INVISIBLE索引关键字，该特性从MariaDB 10.6才正式引入。原语句中第9行的VISIBLE属于无效语法，导致数据库解析后续SQL时出现混乱，最终触发1064错误。

修正后的SQL语句 #

去掉索引定义后的VISIBLE关键字即可正常执行：

CREATE TABLE IF NOT EXISTS `art_gallery`.`artwork` (
  `artwork_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `artyear` INT(4) NOT NULL,
  `period` VARCHAR(25) NULL,
  `arttype` VARCHAR(20) NULL,
  `artfile` VARCHAR(25) NOT NULL,
  `artist_id` INT NOT NULL,
  PRIMARY KEY (`artwork_id`),
  INDEX `fk_artwork_artist_idx` (`artist_id` ASC),
  CONSTRAINT `fk_artwork_artist`
    FOREIGN KEY (`artist_id`)
    REFERENCES `art_gallery`.`artist` (`artist_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

内容的提问来源于stack exchange，提问作者jitterypizza301