I am creating a database with an EER Diagram. This is the code that is created o
ID: 3858930 • Letter: I
Question
I am creating a database with an EER Diagram. This is the code that is created once it is forward engineered:
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 www
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `www` ;
-- -----------------------------------------------------
-- Schema www
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `www` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `www` ;
-- -----------------------------------------------------
-- Table `www`.`trip_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`trip_type` ;
CREATE TABLE IF NOT EXISTS `www`.`trip_type` (
`trip_type_code` INT(11) NOT NULL,
`trip_type_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`trip_type_code`),
UNIQUE INDEX `trip_type_name_UNIQUE` (`trip_type_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`destination`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`destination` ;
CREATE TABLE IF NOT EXISTS `www`.`destination` (
`destination_code` INT(11) NOT NULL,
`destination_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`destination_code`),
UNIQUE INDEX `destination_name_UNIQUE` (`destination_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`gathering_point`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`gathering_point` ;
CREATE TABLE IF NOT EXISTS `www`.`gathering_point` (
`gathering_point_id` INT(11) NOT NULL,
`gathering_point_description` VARCHAR(255) NOT NULL,
PRIMARY KEY (`gathering_point_id`),
UNIQUE INDEX `gathering_point_description_UNIQUE` (`gathering_point_description` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`experience`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`experience` ;
CREATE TABLE IF NOT EXISTS `www`.`experience` (
`experience_code` INT(11) NOT NULL,
`experience_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`experience_code`),
UNIQUE INDEX `experience_name_UNIQUE` (`experience_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`role` ;
CREATE TABLE IF NOT EXISTS `www`.`role` (
`role_code` INT(11) NOT NULL,
`role_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`role_code`),
UNIQUE INDEX `role_name_UNIQUE` (`role_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`guest`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`guest` ;
CREATE TABLE IF NOT EXISTS `www`.`guest` (
`guest_id` INT(11) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`age` INT(11) NOT NULL,
`weight` INT(11) NOT NULL,
`isSwimmer` TINYINT(1) NOT NULL,
`mobile_phone` VARCHAR(20) NULL,
`experience_code` INT(11) NOT NULL,
PRIMARY KEY (`guest_id`),
INDEX `experience_code_idx` (`experience_code` ASC),
CONSTRAINT `experience_code`
FOREIGN KEY (`experience_code`)
REFERENCES `www`.`experience` (`experience_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`employee`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`employee` ;
CREATE TABLE IF NOT EXISTS `www`.`employee` (
`employee_id` INT(11) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`nickname` VARCHAR(45) NOT NULL,
`mobile_phone` VARCHAR(20) NULL,
`home_phone` VARCHAR(20) NULL,
`availability_notes` VARCHAR(255) NOT NULL,
PRIMARY KEY (`employee_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`plays_role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`plays_role` ;
CREATE TABLE IF NOT EXISTS `www`.`plays_role` (
`employee_id` INT(11) NOT NULL,
`role_code` INT(11) NOT NULL,
PRIMARY KEY (`employee_id`, `role_code`),
INDEX `role_code_idx` (`role_code` ASC),
CONSTRAINT `employee_id`
FOREIGN KEY (`employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `role_code`
FOREIGN KEY (`role_code`)
REFERENCES `www`.`role` (`role_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`trip`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`trip` ;
CREATE TABLE IF NOT EXISTS `www`.`trip` (
`trip_number` INT(11) NOT NULL,
`trip_date` DATE NOT NULL,
`capacity` INT(11) NOT NULL,
`latest_guest_arrival_time` TIME NOT NULL,
`departure_time` TIME NOT NULL,
`estimated_return_time` TIME NULL,
`destination_code` INT(11) NOT NULL,
`trip_type_code` INT(11) NOT NULL,
`guide_employee_id` INT(11) NOT NULL,
`gear_employee_id` INT NULL,
`gathering_point_id` INT(11) NOT NULL,
PRIMARY KEY (`trip_number`),
INDEX `destination_code_idx` (`destination_code` ASC),
INDEX `trip_type_code_idx` (`trip_type_code` ASC),
INDEX `employee_id_idx` (`guide_employee_id` ASC),
INDEX `employee_id_idx1` (`gear_employee_id` ASC),
INDEX `gathering_point_id_idx` (`gathering_point_id` ASC),
CONSTRAINT `destination_code`
FOREIGN KEY (`destination_code`)
REFERENCES `www`.`destination` (`destination_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `trip_type_code`
FOREIGN KEY (`trip_type_code`)
REFERENCES `www`.`trip_type` (`trip_type_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `employee_id`
FOREIGN KEY (`guide_employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `employee_id`
FOREIGN KEY (`gear_employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `gathering_point_id`
FOREIGN KEY (`gathering_point_id`)
REFERENCES `www`.`gathering_point` (`gathering_point_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`reservation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`reservation` ;
CREATE TABLE IF NOT EXISTS `www`.`reservation` (
`trip_number` INT(11) NOT NULL,
`guest_id` INT(11) NOT NULL,
PRIMARY KEY (`trip_number`, `guest_id`),
INDEX `guest_id_idx` (`guest_id` ASC),
CONSTRAINT `trip_number`
FOREIGN KEY (`trip_number`)
REFERENCES `www`.`trip` (`trip_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `guest_id`
FOREIGN KEY (`guest_id`)
REFERENCES `www`.`guest` (`guest_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`can_guide`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`can_guide` ;
CREATE TABLE IF NOT EXISTS `www`.`can_guide` (
`employee_id` INT(11) NOT NULL,
`trip_type_code` INT(11) NOT NULL,
PRIMARY KEY (`employee_id`, `trip_type_code`),
INDEX `trip_type_code_idx` (`trip_type_code` ASC),
CONSTRAINT `employee_id`
FOREIGN KEY (`employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `trip_type_code`
FOREIGN KEY (`trip_type_code`)
REFERENCES `www`.`trip_type` (`trip_type_code`)
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;
When I forward engineer it to come up with the database, it says this error code:
Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
CREATE TABLE IF NOT EXISTS `www`.`trip` (
`trip_number` INT(11) NOT NULL,
`trip_date` DATE NOT NULL,
`capacity` INT(11) NOT NULL,
`latest_guest_arrival_time` TIME NOT NULL,
`departure_time` TIME NOT NULL,
`estimated_return_time` TIME NULL,
`destination_code` INT(11) NOT NULL,
`trip_type_code` INT(11) NOT NULL,
`guide_employee_id` INT(11) NOT NULL,
`gear_employee_id` INT NULL,
`gathering_point_id` INT(11) NOT NULL,
PRIMARY KEY (`trip_number`),
INDEX `destination_code_idx` (`destination_code` ASC),
INDEX `trip_type_code_idx` (`trip_type_code` ASC),
INDEX `employee_id_idx` (`guide_employee_id` ASC),
INDEX `employee_id_idx1` (`gear_employee_id` ASC),
INDEX `gathering_point_id_idx` (`gathering_point_id` ASC),
CONSTRAINT `destination_code`
FOREIGN KEY (`destination_code`)
REFERENCES `www`.`destination` (`destination_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `trip_type_code`
FOREIGN KEY (`trip_type_code`)
REFERENCES `www`.`trip_type` (`trip_type_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `employee_id`
FOREIGN KEY (`guide_employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `employee_id`
FOREIGN KEY (`gear_employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `gathering_point_id`
FOREIGN KEY (`gathering_point_id`)
REFERENCES `www`.`gathering_point` (`gathering_point_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
SQL script execution finished: statements: 23 succeeded, 1 failed
Fetching back view definitions in final form.
Nothing to fetch
I'm not sure what I am doing wrong. The EER Diagram I created looks exactly as my professor wants it to look. If anyone can help me with the next step, that would be great.
Thanks!
Explanation / Answer
Answer: The issues with your script are as follows:
1. In trip table, you have specified foreign key constraints' names for columns guide_employee_id and gear_employee_id with the same name as 'employee_id' which is causing the problem. These names should be different. Different columns in a table can reference the same column in parent table, but should have different names.
2. Similarly in can_guide table, you are using duplicate constraint names thats why it also gives error.
While creatiing SQL script schema, a good practice is the have constraint names in capital letters. This is more readable and doesn't create conflicts also. And remember, avoid duplicate constraint names across database.
See the updated script below:
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 www
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `www` ;
-- -----------------------------------------------------
-- Schema www
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `www` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `www` ;
-- -----------------------------------------------------
-- Table `www`.`trip_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`trip_type` ;
CREATE TABLE IF NOT EXISTS `www`.`trip_type` (
`trip_type_code` INT(11) NOT NULL,
`trip_type_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`trip_type_code`),
UNIQUE INDEX `trip_type_name_UNIQUE` (`trip_type_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`destination`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`destination` ;
CREATE TABLE IF NOT EXISTS `www`.`destination` (
`destination_code` INT(11) NOT NULL,
`destination_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`destination_code`),
UNIQUE INDEX `destination_name_UNIQUE` (`destination_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`gathering_point`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`gathering_point` ;
CREATE TABLE IF NOT EXISTS `www`.`gathering_point` (
`gathering_point_id` INT(11) NOT NULL,
`gathering_point_description` VARCHAR(255) NOT NULL,
PRIMARY KEY (`gathering_point_id`),
UNIQUE INDEX `gathering_point_description_UNIQUE` (`gathering_point_description` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`experience`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`experience` ;
CREATE TABLE IF NOT EXISTS `www`.`experience` (
`experience_code` INT(11) NOT NULL,
`experience_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`experience_code`),
UNIQUE INDEX `experience_name_UNIQUE` (`experience_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`role` ;
CREATE TABLE IF NOT EXISTS `www`.`role` (
`role_code` INT(11) NOT NULL,
`role_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`role_code`),
UNIQUE INDEX `role_name_UNIQUE` (`role_name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`guest`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`guest` ;
CREATE TABLE IF NOT EXISTS `www`.`guest` (
`guest_id` INT(11) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`age` INT(11) NOT NULL,
`weight` INT(11) NOT NULL,
`isSwimmer` TINYINT(1) NOT NULL,
`mobile_phone` VARCHAR(20) NULL,
`experience_code` INT(11) NOT NULL,
PRIMARY KEY (`guest_id`),
INDEX `experience_code_idx` (`experience_code` ASC),
CONSTRAINT `experience_code`
FOREIGN KEY (`experience_code`)
REFERENCES `www`.`experience` (`experience_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`employee`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`employee` ;
CREATE TABLE IF NOT EXISTS `www`.`employee` (
`employee_id` INT(11) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`nickname` VARCHAR(45) NOT NULL,
`mobile_phone` VARCHAR(20) NULL,
`home_phone` VARCHAR(20) NULL,
`availability_notes` VARCHAR(255) NOT NULL,
PRIMARY KEY (`employee_id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`plays_role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`plays_role` ;
CREATE TABLE IF NOT EXISTS `www`.`plays_role` (
`employee_id` INT(11) NOT NULL,
`role_code` INT(11) NOT NULL,
PRIMARY KEY (`employee_id`, `role_code`),
INDEX `role_code_idx` (`role_code` ASC),
CONSTRAINT `employee_id`
FOREIGN KEY (`employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `role_code`
FOREIGN KEY (`role_code`)
REFERENCES `www`.`role` (`role_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`trip`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`trip` ;
CREATE TABLE IF NOT EXISTS `www`.`trip` (
`trip_number` INT(11) NOT NULL,
`trip_date` DATE NOT NULL,
`capacity` INT(11) NOT NULL,
`latest_guest_arrival_time` TIME NOT NULL,
`departure_time` TIME NOT NULL,
`estimated_return_time` TIME NULL,
`destination_code` INT(11) NOT NULL,
`trip_type_code` INT(11) NOT NULL,
`guide_employee_id` INT(11) NOT NULL,
`gear_employee_id` INT NULL,
`gathering_point_id` INT(11) NOT NULL,
PRIMARY KEY (`trip_number`),
INDEX `destination_code_idx` (`destination_code` ASC),
INDEX `trip_type_code_idx` (`trip_type_code` ASC),
INDEX `employee_id_idx` (`guide_employee_id` ASC),
INDEX `employee_id_idx1` (`gear_employee_id` ASC),
INDEX `gathering_point_id_idx` (`gathering_point_id` ASC),
CONSTRAINT `destination_code`
FOREIGN KEY (`destination_code`)
REFERENCES `www`.`destination` (`destination_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `trip_type_code`
FOREIGN KEY (`trip_type_code`)
REFERENCES `www`.`trip_type` (`trip_type_code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `guide_employee_id`
FOREIGN KEY (`guide_employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `gear_employee_id`
FOREIGN KEY (`gear_employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `gathering_point_id`
FOREIGN KEY (`gathering_point_id`)
REFERENCES `www`.`gathering_point` (`gathering_point_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`reservation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`reservation` ;
CREATE TABLE IF NOT EXISTS `www`.`reservation` (
`trip_number` INT(11) NOT NULL,
`guest_id` INT(11) NOT NULL,
PRIMARY KEY (`trip_number`, `guest_id`),
INDEX `guest_id_idx` (`guest_id` ASC),
CONSTRAINT `trip_number`
FOREIGN KEY (`trip_number`)
REFERENCES `www`.`trip` (`trip_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `guest_id`
FOREIGN KEY (`guest_id`)
REFERENCES `www`.`guest` (`guest_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `www`.`can_guide`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `www`.`can_guide` ;
CREATE TABLE IF NOT EXISTS `www`.`can_guide` (
`employee_id` INT(11) NOT NULL,
`trip_type_code` INT(11) NOT NULL,
PRIMARY KEY (`employee_id`, `trip_type_code`),
INDEX `trip_type_code_idx` (`trip_type_code` ASC),
CONSTRAINT `employeeid`
FOREIGN KEY (`employee_id`)
REFERENCES `www`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `triptypecode`
FOREIGN KEY (`trip_type_code`)
REFERENCES `www`.`trip_type` (`trip_type_code`)
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;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.