MySQL Workbench中的正向工程输出错误1064 [英] Forward Engineering in MySQL Workbench outputs error 1064
问题描述
这是MySQL Workbench在进行正向工程设计时创建的脚本:
This is the script that MySQL Workbench creates when forward engineering:
-- 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 DialogueFlowDB
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema DialogueFlowDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `DialogueFlowDB` DEFAULT CHARACTER SET utf8 ;
USE `DialogueFlowDB` ;
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`Status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Status` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Status` CHAR(255) NOT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`MultiStep_Dialogues`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`MultiStep_Dialogues` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`MultiStep_Dialogue` CHAR(255) NOT NULL,
`Data_Items` VARCHAR(1000) NULL,
`Nodes` VARCHAR(1000) NULL,
`Node_Links` VARCHAR(1000) NULL,
`Comments` VARCHAR(1000) NULL,
`Status` INT UNSIGNED NOT NULL,
`Issue` INT NOT NULL,
`LO_Name` CHAR(255) NULL,
`LO_Description` CHAR(255) NULL,
`LO_Condition` VARCHAR(1000) NULL,
`LO_Variables` VARCHAR(1000) NULL,
`LO_List` INT NULL,
PRIMARY KEY (`ID`),
INDEX `fk_MultiStep_Dialogues_Status1_idx` (`Status` ASC) VISIBLE,
CONSTRAINT `fk_MultiStep_Dialogues_Status1`
FOREIGN KEY (`Status`)
REFERENCES `DialogueFlowDB`.`Status` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`Node_Types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Node_Types` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Node_Type` CHAR(255) NOT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`Variables`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Variables` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Variable` CHAR(255) NOT NULL,
`Variable_ID` CHAR(255) NOT NULL,
`System_Variable` CHAR(255) NULL,
`Values_per_Item` ENUM('Single', 'Multiple') NOT NULL DEFAULT 'Single',
`MultiStep_Dialogue` INT UNSIGNED NOT NULL,
`Input_Values` VARCHAR(1000) NULL,
`Comments` VARCHAR(1000) NULL,
PRIMARY KEY (`ID`),
INDEX `fk_Variables_MultiStep_Dialogues1_idx` (`MultiStep_Dialogue` ASC) VISIBLE,
CONSTRAINT `fk_Variables_MultiStep_Dialogues1`
FOREIGN KEY (`MultiStep_Dialogue`)
REFERENCES `DialogueFlowDB`.`MultiStep_Dialogues` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`Nodes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Nodes` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Node` CHAR(255) NOT NULL,
`Node_ID` CHAR(255) NOT NULL,
`Node_Sequence` ENUM('End', 'Middle', 'Start') NOT NULL,
`Node_Type` INT UNSIGNED NOT NULL,
`XPosition` INT NOT NULL DEFAULT 0,
`YPosition` INT NOT NULL DEFAULT 0,
`Answer` VARCHAR(1000) NOT NULL,
`Data_Item` INT UNSIGNED NULL,
`Status` INT UNSIGNED NOT NULL DEFAULT 'Created',
`MultiStep_Dialogue` INT UNSIGNED NOT NULL,
`Comments` VARCHAR(1000) NULL,
`Previous_Node_Links` VARCHAR(1000) NULL,
`Next_Node_Link` VARCHAR(100) NULL,
PRIMARY KEY (`ID`),
INDEX `fk_Nodes_MultiStep_Dialogues_idx` (`MultiStep_Dialogue` ASC) VISIBLE,
INDEX `fk_Nodes_Node_Types1_idx` (`Node_Type` ASC) VISIBLE,
INDEX `fk_Nodes_Variables1_idx` (`Data_Item` ASC) VISIBLE,
INDEX `fk_Nodes_Status1_idx` (`Status` ASC) VISIBLE,
CONSTRAINT `fk_Nodes_MultiStep_Dialogues`
FOREIGN KEY (`MultiStep_Dialogue`)
REFERENCES `DialogueFlowDB`.`MultiStep_Dialogues` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Nodes_Node_Types1`
FOREIGN KEY (`Node_Type`)
REFERENCES `DialogueFlowDB`.`Node_Types` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Nodes_Status1`
FOREIGN KEY (`Status`)
REFERENCES `DialogueFlowDB`.`Status` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Nodes_Variables1`
FOREIGN KEY (`Data_Item`)
REFERENCES `DialogueFlowDB`.`Variables` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`Node_Links`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Node_Links` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Node_Link` CHAR(255) NOT NULL,
`MultiStep_Dialogue` INT UNSIGNED NOT NULL,
`From_Node` INT UNSIGNED NOT NULL,
`To_Node` INT UNSIGNED NOT NULL,
`Status` INT UNSIGNED NOT NULL,
`Comments` VARCHAR(1000) NULL,
PRIMARY KEY (`ID`),
INDEX `fk_Node_Links_Nodes1_idx` (`From_Node` ASC) VISIBLE,
INDEX `fk_Node_Links_MultiStep_Dialogues1_idx` (`MultiStep_Dialogue` ASC) VISIBLE,
INDEX `fk_Node_Links_Nodes2_idx` (`To_Node` ASC) VISIBLE,
INDEX `fk_Node_Links_Status1_idx` (`Status` ASC) VISIBLE,
CONSTRAINT `fk_Node_Links_Nodes1`
FOREIGN KEY (`From_Node`)
REFERENCES `DialogueFlowDB`.`Nodes` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Node_Links_MultiStep_Dialogues1`
FOREIGN KEY (`MultiStep_Dialogue`)
REFERENCES `DialogueFlowDB`.`MultiStep_Dialogues` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Node_Links_Nodes2`
FOREIGN KEY (`To_Node`)
REFERENCES `DialogueFlowDB`.`Nodes` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Node_Links_Status1`
FOREIGN KEY (`Status`)
REFERENCES `DialogueFlowDB`.`Status` (`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;
这是输出:
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_MultiStep_Dialogues_Status1`
FOREIGN KEY (`Status`)
RE' at line 19
SQL Code:
-- -----------------------------------------------------
-- Table `DialogueFlowDB`.`MultiStep_Dialogues`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`MultiStep_Dialogues` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`MultiStep_Dialogue` CHAR(255) NOT NULL,
`Data_Items` VARCHAR(1000) NULL,
`Nodes` VARCHAR(1000) NULL,
`Node_Links` VARCHAR(1000) NULL,
`Comments` VARCHAR(1000) NULL,
`Status` INT UNSIGNED NOT NULL,
`Issue` INT NOT NULL,
`LO_Name` CHAR(255) NULL,
`LO_Description` CHAR(255) NULL,
`LO_Condition` VARCHAR(1000) NULL,
`LO_Variables` VARCHAR(1000) NULL,
`LO_List` INT NULL,
PRIMARY KEY (`ID`),
INDEX `fk_MultiStep_Dialogues_Status1_idx` (`Status` ASC) VISIBLE,
CONSTRAINT `fk_MultiStep_Dialogues_Status1`
FOREIGN KEY (`Status`)
REFERENCES `DialogueFlowDB`.`Status` (`ID`)
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
我在线阅读了一下,问题可能出在外键上(尽管我检查了一下,看起来还不错...)或与engine = InnoDB声明有关.我尝试在此问题中使用答案,但没有成功,或者我不太明白答案(我将ENGINE = InnoDB替换为ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;)我还检查了我的mysql版本,我使用10.1.35-MariaDB,它应该有不可见的索引可用(如我所链接问题的另一个答案中所述).
I read online and the issue might be with the Foreign Keys (Although I checked and it seems good...) or related to the engine = InnoDB declaration. I tried to use the answer in this question but it didn't work or I didn't quite understand the answer (I swapped the ENGINE = InnoDB; with ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ) I also checked for my version of mysql, I use 10.1.35-MariaDB which should have invisible indexes available (as mentioned on another answer to the question I linked).
我从没有过使用mySQL的经验,因此,如果这是一个非常简单的问题,我感到抱歉.感谢您的帮助:)
I have no experience with mySQL what so ever, so I'm sorry if this is a very simple problem. Thanks for your help :)
推荐答案
好的,所以有2个不同的问题. DialogueFlow表中Unsigned Int"Status"的默认值为"Created"(我从使用ENUM更改为使用外键,忘了删除旧的默认值),所以它是
Alright so there were 2 different problems. The default value of the Unsigned Int "Status" in DialogueFlow table was "Created" (I changed from using a ENUM to using a foreign key, forgot to remove the old default) so it was
Status
INT UNSIGNED NOT NULL DEFAULT'Created'
Status
INT UNSIGNED NOT NULL DEFAULT 'Created'
另一个问题似乎与索引有关.删除所有内容时:
The other problem seems to be with indexes. When removing all the:
索引
fk_MultiStep_Dialogues_Status1_idx
(Status
ASC)可见
INDEX
fk_MultiStep_Dialogues_Status1_idx
(Status
ASC) VISIBLE
索引fk_Nodes_MultiStep_Dialogues_idx
(MultiStep_Dialogue
ASC)可见,
INDEX fk_Nodes_MultiStep_Dialogues_idx
(MultiStep_Dialogue
ASC) VISIBLE,
索引fk_Nodes_Node_Types1_idx
(Node_Type
ASC)可见,
INDEX fk_Nodes_Node_Types1_idx
(Node_Type
ASC) VISIBLE,
以此类推,它似乎可以工作.我真的不明白为什么会发生这种情况,但是现在我只删除外键上的索引,以后再修复它.
And so on, it seems to work. I don't really understand why that happens but for now I will just remove the indexing on the Foreign Keys and fix it later.
这篇关于MySQL Workbench中的正向工程输出错误1064的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!