sql脚本无法创建某些表

4dbbbstv  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(300)

我的sql脚本在打开它时首先说它没有连接,所以我通过进入query->reconnect to server来连接它。然后我尝试执行只创建前两个表的sql脚本,但遇到了错误代码1064,错误出现在可见的索引行上。我不知道这意味着什么,因为我对sql完全陌生,有人能帮我吗?
错误代码:1064。sql语法有错误;检查与您的mariadb服务器版本对应的手册,以获得在第9行“index userid\u idx(userid asc)visible,constraint gameid foreign”附近使用的正确语法
--mysql工作台生成的mysql脚本——2019年1月2日星期三02:21:44——模型:新模型版本:1.0——mysql工作台正向工程

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_review` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `game_review` ;

-- -----------------------------------------------------
-- Table `game_review`.`games`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`games` (
  `game_Id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `tags` VARCHAR(60) NOT NULL,
  `systemReq` VARCHAR(255) NOT NULL,
  `developer` VARCHAR(45) NOT NULL,
  `publisher` VARCHAR(45) NOT NULL,
  `trailer` VARCHAR(45) NULL,
  `about` VARCHAR(255) NULL,
  `platform` VARCHAR(45) NOT NULL,
  `categories` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  `releaseDate` DATETIME NOT NULL,
  PRIMARY KEY (`game_Id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `game_review`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`users` (
  `user_Id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_Id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `game_review`.`reviews`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`reviews` (
  `review_Id` INT NOT NULL AUTO_INCREMENT,
  `gameId` INT NOT NULL,
  `userId` INT NOT NULL,
  `review` TEXT(1024) NOT NULL,
  `rating` INT NULL,
  `dateposted` DATETIME NULL,
  PRIMARY KEY (`review_Id`),
  INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
  INDEX `userId_idx` (`userId` ASC) VISIBLE,
  CONSTRAINT `gameId`
    FOREIGN KEY (`gameId`)
    REFERENCES `game_review`.`games` (`game_Id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `userId`
    FOREIGN KEY (`userId`)
    REFERENCES `game_review`.`users` (`user_Id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `game_review`.`favourites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`favourites` (
  `favourites_Id` INT NOT NULL AUTO_INCREMENT,
  `gameId` INT NOT NULL,
  `userId` INT NOT NULL,
  PRIMARY KEY (`favourites_Id`),
  INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
  INDEX `userId_idx` (`userId` ASC) VISIBLE,
  CONSTRAINT `gameId`
    FOREIGN KEY (`gameId`)
    REFERENCES `game_review`.`games` (`game_Id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `userId`
    FOREIGN KEY (`userId`)
    REFERENCES `game_review`.`users` (`user_Id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `game_review`.`forum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`forum` (
  `forum_Id` INT NOT NULL AUTO_INCREMENT,
  `gameId` INT NOT NULL,
  `userId` INT NOT NULL,
  `topic` VARCHAR(45) NOT NULL,
  `views` INT NOT NULL,
  `likes` INT NOT NULL,
  `comment` TEXT(300) NULL,
  `totalComments` INT NULL,
  `dateposted` DATETIME NOT NULL,
  PRIMARY KEY (`forum_Id`),
  INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
  INDEX `userId_idx` (`userId` ASC) VISIBLE,
  CONSTRAINT `gameId`
    FOREIGN KEY (`gameId`)
    REFERENCES `game_review`.`games` (`game_Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `userId`
    FOREIGN KEY (`userId`)
    REFERENCES `game_review`.`users` (`user_Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `game_review`.`game cart`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`game cart` (
  `cart_Id` INT NOT NULL AUTO_INCREMENT,
  `gameId` INT NOT NULL,
  `quantity` INT NULL,
  PRIMARY KEY (`cart_Id`),
  INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
  CONSTRAINT `gameId`
    FOREIGN KEY (`gameId`)
    REFERENCES `game_review`.`games` (`game_Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
dsekswqp

dsekswqp1#

索引声明不正确:visible语法无法识别。此外,提及asc也是多余的,因为它是对违约的回应。
替换:

...
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
...

使用:

...
INDEX `reviews_gameId_idx` (`gameId`),
INDEX `reviews_userId_idx` (`userId`),
...

有关更多信息,请参阅mysql create index docs。
另外,请记住,在除mysql以外的大多数rdbms中,索引名在整个数据库模式中都是全局的,因此请确保不要两次使用同一索引名(我可以看到,至少索引名是全局的) gameId_idx 声明两次。一种解决方案是在索引名前面加上它所属的表,如上所示。
不同表之间的外键名也必须是唯一的(例如 game_Id ).
最后,我还注意到您的一个表名中有一个空格( game cart ):这不是一个好的做法,应该避免。

相关问题