MySQL帮助正向工程

Question

所以我正在尝试将我在 Workbench 6.2 上创建的数据库部署到真实数据库上(以对其进行测试)...

但我每次部署它时都会收到错误，每次我认为我已经解决了这个问题时，都会弹出一些其他错误消息(这次是 1064)..

任何人都可以帮我解决我的数据库问题，以便我最终可以部署它并进行测试吗？ (我是菜鸟所以请耐心等待)..

这就是正向工程试图执行的:

-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema iPlanner
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema iPlanner
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `iPlanner` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `iPlanner` ;

-- -----------------------------------------------------
-- Table `iPlanner`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`users` (
  `user_permission` INT NOT NULL,
  `user_login` VARCHAR(45) NULL,
  `user_pass` VARCHAR(45) NULL,
  `pass_attempts` INT NULL,
  `user_f_name` VARCHAR(45) NULL,
  `user_l_name` VARCHAR(45) NULL,
  `user_email` VARCHAR(75) NULL,
  PRIMARY KEY (`user_permission`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`administrators`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`administrators` (
  `admin_id` INT NOT NULL AUTO_INCREMENT,
  `user_permission` INT NULL,
  PRIMARY KEY (`admin_id`),
  INDEX `user_permission_idx` (`user_permission` ASC),
  CONSTRAINT `user_permission`
    FOREIGN KEY (`user_permission`)
    REFERENCES `iPlanner`.`users` (`user_permission`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_login`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_pass`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pass_attempts`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_f_name`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_l_name`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_email`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`schools`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`schools` (
  `school_id` INT NOT NULL AUTO_INCREMENT,
  `school_name` VARCHAR(45) NULL,
  `school_city` VARCHAR(75) NULL,
  `school_state` VARCHAR(2) NULL,
  `school_zipcode` VARCHAR(45) NULL,
  `principal_id` INT NOT NULL,
  PRIMARY KEY (`school_id`),
  INDEX `principal_id_idx` (`principal_id` ASC),
  CONSTRAINT `principal_id`
    FOREIGN KEY (`principal_id`)
    REFERENCES `iPlanner`.`administrators` (`admin_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`subject`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`subject` (
  `subject_id` INT NOT NULL AUTO_INCREMENT,
  `subject_name` VARCHAR(45) NULL,
  `teacher_id` INT NOT NULL,
  PRIMARY KEY (`subject_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`class`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`class` (
  `class_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `class_name` VARCHAR(75) NULL,
  PRIMARY KEY (`class_id`),
  INDEX `subject_id_idx` (`subject_id` ASC),
  CONSTRAINT `subject_id`
    FOREIGN KEY (`subject_id`)
    REFERENCES `iPlanner`.`subject` (`subject_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`teachers` (
  `teacher_id` INT NOT NULL AUTO_INCREMENT,
  `user_permission` INT NULL,
  PRIMARY KEY (`teacher_id`),
  INDEX `user_permission_idx` (`user_permission` ASC),
  CONSTRAINT `user_permission`
    FOREIGN KEY (`user_permission`)
    REFERENCES `iPlanner`.`users` (`user_permission`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_login`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_pass`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pass_attempts`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_f_name`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_l_name`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_email`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`subject_class`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`subject_class` (
  `sc_id` INT NOT NULL AUTO_INCREMENT,
  `class_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `teacher_id` INT NOT NULL,
  PRIMARY KEY (`sc_id`),
  INDEX `class_id_idx` (`class_id` ASC),
  INDEX `subject_id_idx` (`subject_id` ASC),
  INDEX `teacher_id_idx` (`teacher_id` ASC),
  CONSTRAINT `class_id`
    FOREIGN KEY (`class_id`)
    REFERENCES `iPlanner`.`class` (`class_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `subject_id`
    FOREIGN KEY (`subject_id`)
    REFERENCES `iPlanner`.`subject` (`subject_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `teacher_id`
    FOREIGN KEY (`teacher_id`)
    REFERENCES `iPlanner`.`teachers` (`teacher_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`students`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`students` (
  `student_id` INT NOT NULL AUTO_INCREMENT,
  `user_permission` INT NULL,
  `signature` VARCHAR(150) NULL,
  PRIMARY KEY (`student_id`),
  INDEX `user_permission_idx` (`user_permission` ASC),
  CONSTRAINT `user_permission`
    FOREIGN KEY (`user_permission`)
    REFERENCES `iPlanner`.`users` (`user_permission`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_login`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_pass`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pass_attempts`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_f_name`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_l_name`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_email`
    FOREIGN KEY ()
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`enrollment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`enrollment` (
  `enrollment_id` INT NOT NULL AUTO_INCREMENT,
  `subject_class_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  PRIMARY KEY (`enrollment_id`),
  INDEX `subject_class_id_idx` (`subject_class_id` ASC),
  INDEX `student_id_idx` (`student_id` ASC),
  CONSTRAINT `subject_class_id`
    FOREIGN KEY (`subject_class_id`)
    REFERENCES `iPlanner`.`subject_class` (`sc_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `student_id`
    FOREIGN KEY (`student_id`)
    REFERENCES `iPlanner`.`students` (`student_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`assignments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`assignments` (
  `assignment_id` INT NOT NULL AUTO_INCREMENT,
  `assignment_content` VARCHAR(45) NULL,
  `assignment_due_date` VARCHAR(45) NULL,
  `assignment_date` VARCHAR(45) NULL,
  `assignment_time` VARCHAR(45) NULL,
  `subject_class_id` INT NOT NULL,
  PRIMARY KEY (`assignment_id`),
  INDEX `subject_class_id_idx` (`subject_class_id` ASC),
  CONSTRAINT `subject_class_id`
    FOREIGN KEY (`subject_class_id`)
    REFERENCES `iPlanner`.`subject_class` (`sc_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `iPlanner`.`submits`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `iPlanner`.`submits` (
  `submit_id` INT NOT NULL AUTO_INCREMENT,
  `student_id` INT NOT NULL,
  `assignment_id` INT NOT NULL,
  `notes_teacher` VARCHAR(45) NULL,
  `notes_parents` VARCHAR(45) NULL,
  `parent_signature` VARCHAR(45) NULL,
  `total_time` VARCHAR(45) NULL,
  PRIMARY KEY (`submit_id`),
  INDEX `assignment_id_idx` (`assignment_id` ASC),
  INDEX `student_id_idx` (`student_id` ASC),
  CONSTRAINT `assignment_id`
    FOREIGN KEY (`assignment_id`)
    REFERENCES `iPlanner`.`assignments` (`assignment_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `student_id`
    FOREIGN KEY (`student_id`)
    REFERENCES `iPlanner`.`students` (`student_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 MySQL server version for the right syntax to use near ')
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ' at line 12
SQL Code:
        CREATE TABLE IF NOT EXISTS `iPlanner`.`administrators` (
          `admin_id` INT NOT NULL AUTO_INCREMENT,
          `user_permission` INT NULL,
          PRIMARY KEY (`admin_id`),
          INDEX `user_permission_idx` (`user_permission` ASC),
          CONSTRAINT `user_permission`
            FOREIGN KEY (`user_permission`)
            REFERENCES `iPlanner`.`users` (`user_permission`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_login`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_pass`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `pass_attempts`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_f_name`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_l_name`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_email`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

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

Fetching back view definitions in final form.
Nothing to fetch
Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    REFERENCES `iPlanner`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ' at line 15
SQL Code:
        -- -----------------------------------------------------
        -- Table `iPlanner`.`administrators`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `iPlanner`.`administrators` (
          `admin_id` INT NOT NULL AUTO_INCREMENT,
          `user_permission` INT NULL,
          PRIMARY KEY (`admin_id`),
          INDEX `user_permission_idx` (`user_permission` ASC),
          CONSTRAINT `user_permission`
            FOREIGN KEY (`user_permission`)
            REFERENCES `iPlanner`.`users` (`user_permission`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_login`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_pass`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `pass_attempts`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_f_name`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_l_name`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `user_email`
            FOREIGN KEY ()
            REFERENCES `iPlanner`.`users` ()
            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

编辑 1064 仅表示“语法错误”。如果您在尝试运行由 Workbench 等工具创建的 SQL 时遇到这种情况，则意味着该工具存在错误或您使用不当。

当 MySQL 检测到错误时，它会输出单词 for the right syntax to use near 然后显示您的一些查询，从您查询中它没有的第一个字符开始'不明白。 这是为了帮助您找出问题所在。

在您的问题中，这是一个右括号。这对我来说意味着它期望在 REFERENCES 子句中的括号之间出现一些内容。

在我看来，您的模型的 administrators 表有很多返回 users 表的格式错误的外键。

这只是其中之一。

     CONSTRAINT `user_login`
        FOREIGN KEY ()
        REFERENCES `iPlanner`.`users` ()
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,

注意 ():它引用了表但没有引用列。