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.