MySQL帮助转发工程 [英] MySQL help forward engineering

问题描述

所以我试图将我在Workbench 6.2上创建的数据库部署到一个真实的数据库（以测试它）...

但是我每次我部署它，每次我认为我已经修复该问题一些其他错误信息弹出（这一次是1064）。

任何人都可以帮助我与我的数据库，所以我可以终于部署它和测试的东西吗？ （我是一个noob所以请耐心等待我）..

这是正向工程尝试执行：

   -  MySQL Workbench转发工程
 
 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 
  -  ------------------------------------------- ---------- 
如果不存在，则创建模式'​​iPlanner'DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 
 USE`iPlanner`; 
 
  -  ---------------------------------------- ------------- 
  - 表`iPlanner`.`users` 
  -  ----------------- ------------------------------------ 
如果不存在`iPlanner`.`，则创建表（
`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 ）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`administrators` 
  -  ------------- ---------------------------------------- 
如果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`）
参考`iPlanner`.`users`（`user_permission`）
删除
 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（）
参考`iPlanner`.`users`（）
删除没有操作
 ON UPDATE无操作，
 CONSTRAINT`pass_attempts` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION， 
 CONSTRAINT`user_f_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION，
 CONSTRAINT`user_l_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION，
 CONSTRAINT` user_email` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`schools` 
  -  ------------- ---------------------------------------- 
如果NOT EXISTS`，则创建表（
`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'ASC'，
 CONSTRAINT`principal_id'
 FOREIGN KEY（`principal_id'）
 REFERENCES`iPlanner`.`administrators`（`admin_id`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`subject` 
  -  ------------- ---------------------------------------- 
如果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; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`class` 
  -  ------------- ---------------------------------------- 
如果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`）
参考`iPlanner`.`subject` （`subject_id`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`teachers` 
  -  ------------- ---------------------------------------- 
如果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`）
参考`iPlanner`.`users`（`user_permission`）
删除
 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（）
参考`iPlanner`.`users`（）
删除没有操作
 ON UPDATE无操作，
 CONSTRAINT`pass_attempts` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION， 
 CONSTRAINT`user_f_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION，
 CONSTRAINT`user_l_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION，
 CONSTRAINT` user_email` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`subject_class` 
  -  ------------- ---------------------------------------- 
如果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删除没有操作
 ON UPDATE NO ACTION，
 CONSTRAINT`subject_id` 
 FOREIGN KEY（`subject_id`）
参考`iPlanner`.`subject`（`subject_id`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`teacher_id'
 FOREIGN KEY（`teacher_id'）
参考`iPlanner`.`teachers` ）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`students` 
  -  ------------- ---------------------------------------- 
如果NOT EXISTS`，则创建表（
`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`）
参考`iPlanner`.`users` （`user_permission`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_login` 
 FOREIGN KEY（）
参考`iPlanner`.`users` （）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_pass` 
 FOREIGN KEY（）
 REFERENCES`iPlanner`.`users` b $ b ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`pass_attempts`
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_f_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_l_name`
 FOREIGN KEY（）
 REFERENCES`iPlanner`.`users`（）
 ON删除无操作
 ON UPDATE NO ACTION，
 CONSTRAINT`user_email` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON删除无操作
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`enrollment` 
  -  ------------- ---------------------------------------- 
如果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 `）
参考`iPlanner`.`subject_class`（`sc_id`）
 ON删除操作
 ON UPDATE NO ACTION，
 CONSTRAINT`student_id`
 FOREIGN KEY （`student_id`）
参考`iPlanner`.`students`（`student_id`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`assignments` 
  -  ------------- ---------------------------------------- 
如果NOT EXISTS`，则创建表（
`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; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`iPlanner`.`submits` 
  -  ------------- ---------------------------------------- 
如果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`）
参考`iPlanner`.`assignments`（`assignment_id`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`student_id`
 FOREIGN KEY（`student_id'）
参考`iPlanner`.`students`（`student_id`）
 ON删除没有动作
 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; 
  

这是我得到的当前错误。

 在服务器中执行SQL脚本
错误：错误1064：SQL语法中有错误;检查与您的MySQL服务器版本对应的手册，以获得正确的语法使用near'）
 REFERENCES`iPlanner`.`users`（）
 ON删除无操作
 ON UPDATE NO'行12 
 SQL代码：
如果不存在，则使用CREATE TABLE IFlanner`.`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（）
参考`iPlanner`.`users` （）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_f_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users` b $ b ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_l_name`
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_email` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB 
 
 SQL脚本执行完成：语句：8成功，1失败
 
获取视图定义最终形式。 
无法获取
在服务器中执行SQL脚本
错误：错误1064：SQL语法中有错误;检查对应于您的MySQL服务器版本的手册，以获得正确的语法使用near'）
 REFERENCES`iPlanner`.`users`（）
 ON删除无操作
 ON UPDATE NO'行15 
 SQL代码：
  -  ----------------------------------- ------------------ 
  - 表`iPlanner`.`administrators` 
  -  ------------ ----------------------------------------- 
如果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`）
参考`iPlanner`.`users`（`user_permission`）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
 CONSTRAINT`user_login` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除没有动作
 ON UPDATE NO ACTION，
 CONSTRAINT`user_pass` 
 FOREIGN KEY（）
参考文献iPlanner`.`users`（）
删除没有动作
 ON UPDATE NO ACTION，
 CONSTRAINT`pass_attempts` 
 FOREIGN KEY（）
参考文献iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION ，
 CONSTRAINT`user_f_name` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
删除无操作
 ON UPDATE NO ACTION，
 CONSTRAINT`user_l_name` 
 FOREIGN KEY（）
参考文献iPlanner`.`users`（）
删除没有动作
 ON UPDATE NO ACTION，
 CONSTRAINT `user_email` 
 FOREIGN KEY（）
参考`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION）
 ENGINE = InnoDB 
 
 SQL脚本执行完成：语句：6成功，1失败
 
以最终形式获取视图定义。 
无法获取
  

解决方案

1064只是指语法错误。如果你遇到这样的情况，当你尝试运行由工具，如Workbench创建的SQL，这意味着该工具有一个错误或你使用不正确。

当MySQL检测到错误时，它会输出字样以在附近使用正确的语法， 这是为了帮助您找出您的问题所在。

p>在你的问题中，这是一个右括号。这意味着我希望在 REFERENCES 子句中的括号之间出现一些东西。

在我看来，你的模型的管理员表有很多错误的外键返回 users 表。

这里只是其中一个。

  CONSTRAINT`user_login` 
 FOREIGN KEY（）
 REFERENCES`iPlanner`.`users`（）
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION，
  

请注意（）：它引用表，但没有列。

So I'm trying to deploy the database that I've created on workbench 6.2 onto a real database (to test it out)...

But I keep on getting errors every time I deploy it, and every time I think I've fixed the issue some other error message pops up (this time it's 1064)..

Can anyone please help me out with my database so I can finally deploy it and test things out? (I'm a noob so please be patient with me)..

This is what the forward-engineering is trying to execute:

-- 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;

And this is the current error that I'm getting..

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

解决方案

Edit 1064 simply means "syntax error." If you're getting that when you try to run SQL created by a tool like Workbench, it either means the tool has a bug or you've used it incorrectly.

When MySQL detects the error, it puts out the words for the right syntax to use near and then shows some of your query, starting with the first character in your query it didn't understand. That's to help you figure out where your problem might lie.

In your question, that's a close parenthesis. That means to me that it's expecting something to appear between your parentheses in your REFERENCES clause.

It looks to me like your model's administrators table has a lot of ill-formed foreign keys back to the users table.

Here's just one of them.

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

Notice the (): It references the table but no column.

这篇关于MySQL帮助转发工程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！