mariadb - 来自正向工程 EERD 的 Workbench 8.0 中的错误代码 1064
问题描述
我从我的正向工程数据库中收到以下错误:
错误代码:1064。您的 SQL 语法有错误;检查与您的 MariaDB 服务器版本相对应的手册,以在“约束fk_Employees_EmployeeTitle1
外键”附近使用正确的语法(第 9 行的“EmployeeTitle_EmpT”
代码对我来说看起来不错,但我不知道从哪里开始让它工作。代码如下:
-- MySQL Workbench Forward Engineering
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 A2Lewis
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema A2Lewis
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `A2Lewis` DEFAULT CHARACTER SET utf8 ;
USE `A2Lewis` ;
-- -----------------------------------------------------
-- Table `A2Lewis`.`EmployeeTitle`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`EmployeeTitle` (
`EmpTitleID` INT NOT NULL,
`EmpTitle` VARCHAR(45) NULL,
PRIMARY KEY (`EmpTitleID`))
ENGINE = InnoDB;
INSERT INTO EmployeeTitle VALUES
(01, "Sales Representative"),
(02, "Tour Guide");
-- -----------------------------------------------------
-- Table `A2Lewis`.`Employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Employees` (
`empID` INT NOT NULL,
`empFirst` VARCHAR(45) NULL,
`empLast` VARCHAR(45) NULL,
`empWage` DECIMAL(4,2) NULL,
`empPhone` CHAR(10) NULL,
`EmployeeTitle_EmpTitleID` INT NOT NULL,
PRIMARY KEY (`empID`),
INDEX `fk_Employees_EmployeeTitle1_idx` (`EmployeeTitle_EmpTitleID` ASC) VISIBLE,
CONSTRAINT `fk_Employees_EmployeeTitle1`
FOREIGN KEY (`EmployeeTitle_EmpTitleID`)
REFERENCES `A2Lewis`.`EmployeeTitle` (`EmpTitleID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO Employees VALUES
(01, "Thomas", "Lewis", 12.00, 6134493075, 01),
(02, "John", "Doe", 11.25, 6135554267, 01),
(03, "Sarah", "Simon", 11.00, 6135554582, 01),
(04, "Connor", "Toth", 15.00, 6135557894, 02),
(05, "James", "Young", 13.50, 6135554528, 02),
(06, "Ben", "Zimmer", 14.75, 6135554287, 02);
-- -----------------------------------------------------
-- Table `A2Lewis`.`Tours`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Tours` (
`tourID` INT NOT NULL,
`tourName` VARCHAR(45) NULL,
`tourMeetLoc` VARCHAR(45) NULL,
`tourDist` INT NULL,
`tourMaxCust` INT NULL,
`tourCost` DECIMAL(5,2) NULL,
`tourProvince` VARCHAR(45) NULL,
`tourDate` DATE NULL,
PRIMARY KEY (`tourID`))
ENGINE = InnoDB;
INSERT INTO Tours VALUES
(01, "Sightseeing tour of Halifax", "City of Halifax", 15, 35, 585.00, "Nova Scotia", 2019-03-05),
(02, "The Sun and Sand Trail", "City of Bathurst", 250, 20, 999.99, "New Brunswick", 2019-07-10),
(03, "Quebec Countryside Tour", "Chemin du Roy", 89, 30, 800.00, "Quebec", 2019-10-15);
-- -----------------------------------------------------
-- Table `A2Lewis`.`Seasons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Seasons` (
`seasID` INT NOT NULL,
`seasName` VARCHAR(45) NULL,
PRIMARY KEY (`seasID`))
ENGINE = InnoDB;
INSERT INTO Seasons VALUES
(01, "Early Spring"),
(02, "Mid Spring"),
(03, "Late Spring"),
(04, "Early Summer"),
(05, "Mid Summer"),
(06, "Late Summer"),
(07, "Early Fall"),
(08, "Mid Fall"),
(09, "Late Fall");
-- -----------------------------------------------------
-- Table `A2Lewis`.`Equipment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Equipment` (
`equipID` INT NOT NULL,
`equipDescr` VARCHAR(45) NULL,
`equipPrice` DECIMAL(4,2) NULL,
`Tours_tourID` INT NOT NULL,
PRIMARY KEY (`equipID`),
INDEX `fk_Equipment_Tours1_idx` (`Tours_tourID` ASC) VISIBLE,
CONSTRAINT `fk_Equipment_Tours1`
FOREIGN KEY (`Tours_tourID`)
REFERENCES `A2Lewis`.`Tours` (`tourID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO Equipment VALUES
(01, "Four Person Tent", 89.00, 02),
(02, "Adult Bicycle", 50.00, 03);
-- -----------------------------------------------------
-- Table `A2Lewis`.`Customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Customers` (
`custID` INT NOT NULL,
`custFirst` VARCHAR(45) NOT NULL,
`custLast` VARCHAR(45) NOT NULL,
`cust65` TINYINT NOT NULL,
`custStud` TINYINT NOT NULL,
PRIMARY KEY (`custID`))
ENGINE = InnoDB;
INSERT INTO Customers VALUES
(01, "Thomas", "O'Neill", 0, 0),
(02, "Melissa", "Kennedy", 0, 1),
(03, "Craig", "Smith", 0, 0);
-- -----------------------------------------------------
-- Table `A2Lewis`.`Reservation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Reservation` (
`reservID` INT NOT NULL,
`Tours_tourID` INT NOT NULL,
`Employees_empID` INT NOT NULL,
`Customers_custID` INT NOT NULL,
PRIMARY KEY (`reservID`, `Employees_empID`),
INDEX `fk_Booking_Tours1_idx` (`Tours_tourID` ASC) VISIBLE,
INDEX `fk_Booking_Employees1_idx` (`Employees_empID` ASC) VISIBLE,
INDEX `fk_Reservation_Customers1_idx` (`Customers_custID` ASC) VISIBLE,
CONSTRAINT `fk_Booking_Tours1`
FOREIGN KEY (`Tours_tourID`)
REFERENCES `A2Lewis`.`Tours` (`tourID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Booking_Employees1`
FOREIGN KEY (`Employees_empID`)
REFERENCES `A2Lewis`.`Employees` (`empID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Reservation_Customers1`
FOREIGN KEY (`Customers_custID`)
REFERENCES `A2Lewis`.`Customers` (`custID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO Reservation VALUES
(01, 01, 04, 01),
(02, 02, 05, 02),
(03, 03, 06, 03);
-- -----------------------------------------------------
-- Table `A2Lewis`.`Tours_has_Seasons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Tours_has_Seasons` (
`Tours_tourID` INT NOT NULL,
`Seasons_seasID` INT NOT NULL,
PRIMARY KEY (`Tours_tourID`, `Seasons_seasID`),
INDEX `fk_Tours_has_Seasons_Seasons1_idx` (`Seasons_seasID` ASC) VISIBLE,
INDEX `fk_Tours_has_Seasons_Tours1_idx` (`Tours_tourID` ASC) VISIBLE,
CONSTRAINT `fk_Tours_has_Seasons_Tours1`
FOREIGN KEY (`Tours_tourID`)
REFERENCES `A2Lewis`.`Tours` (`tourID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Tours_has_Seasons_Seasons1`
FOREIGN KEY (`Seasons_seasID`)
REFERENCES `A2Lewis`.`Seasons` (`seasID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO Tours_has_Seasons VALUES
(01,01),
(02,05),
(03,07);
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
解决方案
该错误是由于 MySQL 和 MariaDB 之间的不兼容引起的。
该脚本显然适用于 MySQL,但它在 MariaDB 上运行。尽管 MariaDB 最初与 MySQL 100% 兼容,但现在两个数据库已经出现了分歧。许多人现在认为 MariaDB 更先进,但事实是它们现在到处都有小/大的差异。
在这种情况下,创建索引时,VISIBLE
MariaDB 不支持该关键字。而不是使用:
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Employees` (
`empID` INT NOT NULL,
`empFirst` VARCHAR(45) NULL,
`empLast` VARCHAR(45) NULL,
`empWage` DECIMAL(4,2) NULL,
`empPhone` CHAR(10) NULL,
`EmployeeTitle_EmpTitleID` INT NOT NULL,
PRIMARY KEY (`empID`),
INDEX `fk_Employees_EmployeeTitle1_idx` (`EmployeeTitle_EmpTitleID` ASC) VISIBLE,
CONSTRAINT `fk_Employees_EmployeeTitle1`
FOREIGN KEY (`EmployeeTitle_EmpTitleID`)
REFERENCES `A2Lewis`.`EmployeeTitle` (`EmpTitleID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
利用:
CREATE TABLE IF NOT EXISTS `A2Lewis`.`Employees` (
`empID` INT NOT NULL,
`empFirst` VARCHAR(45) NULL,
`empLast` VARCHAR(45) NULL,
`empWage` DECIMAL(4,2) NULL,
`empPhone` CHAR(10) NULL,
`EmployeeTitle_EmpTitleID` INT NOT NULL,
PRIMARY KEY (`empID`),
INDEX `fk_Employees_EmployeeTitle1_idx` (`EmployeeTitle_EmpTitleID` ASC),
CONSTRAINT `fk_Employees_EmployeeTitle1`
FOREIGN KEY (`EmployeeTitle_EmpTitleID`)
REFERENCES `A2Lewis`.`EmployeeTitle` (`EmpTitleID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
第二个查询(对于 MariaDB)不需要,VISIBLE
因为索引在 MariaDB 中默认是可见的。
推荐阅读
- rust - 我怎样才能实现`从
?` - node.js - AWS ElasticBeanstalk Amazon Linux 2 .platform 文件夹未复制 NGINX conf
- attributes - Asciidoctor:激活 CLI 属性中的替换
- python - 列表的 DataFrame 列和 NaN 到 String
- java - 无法在邮递员上使用 MTLS 服务,但它可以在 SOAP UI 中使用
- python - pytorch:如何在没有任何条目的情况下正确初始化张量?
- node.js - 特定于域的路由 - Nodejs
- django - 将具有特定属性的自定义用户添加到“朋友”列表
- python - 在 C++ 中创建的 python 对象超出范围?
- javascript - React JS 中的进度条