-- 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 BrendasDMS
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `BrendasDMS` ;
-- -----------------------------------------------------
-- Schema BrendasDMS
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `BrendasDMS` DEFAULT CHARACTER SET utf8 ;
SHOW WARNINGS;
USE `BrendasDMS` ;
-- -----------------------------------------------------
-- Table `Customers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Customers` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Customers` (
`Customer_ID` INT NOT NULL AUTO_INCREMENT,
` FirstName` VARCHAR(45) NOT NULL,
`LastName` VARCHAR(45) NOT NULL,
`Email` VARCHAR(45) NOT NULL,
`Phone` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Customer_ID`),
UNIQUE INDEX `CustomerID_UNIQUE` (`Customer_ID` ASC))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Employees`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Employees` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Employees` (
`Employee_ID` INT NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(45) NOT NULL,
`LastName` VARCHAR(45) NOT NULL,
`Address` VARCHAR(45) NOT NULL,
`City` VARCHAR(45) NOT NULL,
`State` VARCHAR(45) NOT NULL,
`Zip` VARCHAR(45) NOT NULL,
`County` VARCHAR(45) NOT NULL,
`PhoneNumber` VARCHAR(45) NOT NULL,
`BirthDate` DATE NOT NULL,
`SSN` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Employee_ID`),
UNIQUE INDEX `FirstName_UNIQUE` (`FirstName` ASC))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Shops`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Shops` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Shops` (
`Shop_ID` INT NOT NULL AUTO_INCREMENT,
`Address` VARCHAR(45) NOT NULL,
`County` VARCHAR(45) NOT NULL,
`Zip` VARCHAR(45) NOT NULL,
`Phone Number` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Shop_ID`),
UNIQUE INDEX `ID_UNIQUE` (`Shop_ID` ASC))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Sales`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Sales` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Sales` (
`Sale_ID` INT NOT NULL AUTO_INCREMENT,
`SaleDate` DATE NOT NULL,
`Employee_ID` INT NOT NULL,
`Customer_ID` INT NOT NULL,
`Shop_ID` INT NOT NULL,
`Quantity` INT NOT NULL,
`Price` DECIMAL NOT NULL,
`UnitOfMeasure` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Sale_ID`, `Customer_ID`, `Employee_ID`, `Shop_ID`),
INDEX `fk_Sale_Employee1_idx` (`Employee_ID` ASC),
INDEX `fk_Sale_Customer1_idx` (`Customer_ID` ASC),
INDEX `fk_Sale_Shops1_idx` (`Shop_ID` ASC),
CONSTRAINT `fk_Sale_Employee1`
FOREIGN KEY (`Employee_ID`)
REFERENCES `Employees` (`Employee_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Sale_Customer1`
FOREIGN KEY (`Customer_ID`)
REFERENCES `Customers` (`Customer_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Sale_Shops1`
FOREIGN KEY (`Shop_ID`)
REFERENCES `Shops` (`Shop_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Products`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Products` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Products` (
`Product_ID` INT NOT NULL AUTO_INCREMENT,
`ProductName` VARCHAR(45) NOT NULL,
`UnitOfMeasure` VARCHAR(45) NOT NULL,
`Price` DECIMAL NOT NULL,
`Quantity` INT NOT NULL,
PRIMARY KEY (`Product_ID`),
UNIQUE INDEX `RecipeID_UNIQUE` (`Product_ID` ASC))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `SaleLineItems`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SaleLineItems` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `SaleLineItems` (
`Sale_ID` INT NOT NULL,
`Quantity` INT NOT NULL,
`Product_ID` INT NOT NULL,
PRIMARY KEY (`Sale_ID`, `Product_ID`),
INDEX `fk_Sale_has_Product_Sale1_idx` (`Sale_ID` ASC),
INDEX `fk_SaleLineItem_Product1_idx` (`Product_ID` ASC),
CONSTRAINT `fk_Sale_has_Product_Sale1`
FOREIGN KEY (`Sale_ID`)
REFERENCES `Sales` (`Sale_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_SaleLineItem_Product1`
FOREIGN KEY (`Product_ID`)
REFERENCES `Products` (`Product_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Vendors`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Vendors` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Vendors` (
`Vendor_ID` INT NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(45) NOT NULL,
`LastName` VARCHAR(45) NOT NULL,
`VendorType` VARCHAR(45) NOT NULL,
`Address` VARCHAR(45) NOT NULL,
`Zip` VARCHAR(45) NOT NULL,
`PhoneNumber` VARCHAR(45) NOT NULL,
PRIMARY KEY (`Vendor_ID`),
UNIQUE INDEX `ID_UNIQUE` (`Vendor_ID` ASC))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Orders`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Orders` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Orders` (
`Order_ID` INT NOT NULL AUTO_INCREMENT,
`Date` DATE NOT NULL,
`Vendor_ID` INT NOT NULL,
`Quantity` INT NOT NULL,
`UnitOfMeasure` VARCHAR(45) NOT NULL,
`Price` DECIMAL NOT NULL,
PRIMARY KEY (`Order_ID`, `Vendor_ID`),
UNIQUE INDEX `ID_UNIQUE` (`Order_ID` ASC),
INDEX `fk_Orders_Vendor1_idx` (`Vendor_ID` ASC),
CONSTRAINT `fk_Orders_Vendor1`
FOREIGN KEY (`Vendor_ID`)
REFERENCES `Vendors` (`Vendor_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `EmployeeShop`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EmployeeShop` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `EmployeeShop` (
`Shop_ID` INT NOT NULL,
`Employee_ID` INT NOT NULL,
PRIMARY KEY (`Shop_ID`, `Employee_ID`),
INDEX `fk_Shop_has_Employee_Employee1_idx` (`Employee_ID` ASC),
INDEX `fk_Shop_has_Employee_Shop1_idx` (`Shop_ID` ASC),
CONSTRAINT `fk_Shop_has_Employee_Shop1`
FOREIGN KEY (`Shop_ID`)
REFERENCES `Shops` (`Shop_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Shop_has_Employee_Employee1`
FOREIGN KEY (`Employee_ID`)
REFERENCES `Employees` (`Employee_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Ingredients`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Ingredients` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Ingredients` (
`Ingredient_ID` INT NOT NULL,
`IngredientName` VARCHAR(45) NOT NULL,
`UnitOfMeasure` VARCHAR(45) NOT NULL,
`Quantity` INT NOT NULL,
`Price` DECIMAL NOT NULL,
PRIMARY KEY (`Ingredient_ID`))
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `OrderLineItems`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `OrderLineItems` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `OrderLineItems` (
`Quantity` INT NOT NULL,
`Order_ID` INT NOT NULL,
`Ingredient_ID` INT NOT NULL,
PRIMARY KEY (`Order_ID`, `Ingredient_ID`),
INDEX `fk_OrderLineItems_Orders1_idx` (`Order_ID` ASC),
INDEX `fk_OrderLineItems_Ingredients1_idx` (`Ingredient_ID` ASC),
CONSTRAINT `fk_OrderLineItems_Orders1`
FOREIGN KEY (`Order_ID`)
REFERENCES `Orders` (`Order_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_OrderLineItems_Ingredients1`
FOREIGN KEY (`Ingredient_ID`)
REFERENCES `Ingredients` (`Ingredient_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = ndbcluster;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `Recipes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Recipes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `Recipes` (
`Recipe_ID` INT NOT NULL,
`Directions` VARCHAR(45) NOT NULL,
`Product_ID` INT NOT NULL,
PRIMARY KEY (`Recipe_ID`, `Product_ID`),
UNIQUE INDEX `Recipe_ID_UNIQUE` (`Recipe_ID` ASC),
INDEX `fk_Recipes_Products1_idx` (`Product_ID` ASC),
CONSTRAINT `fk_Recipes_Products1`
FOREIGN KEY (`Product_ID`)
REFERENCES `Products` (`Product_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `RecipeIngredients`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `RecipeIngredients` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `RecipeIngredients` (
`Quantity` INT NOT NULL,
`Ingredient_ID` INT NOT NULL,
`UnitOfMeasure` VARCHAR(45) NOT NULL,
`Price` DECIMAL NOT NULL,
`Recipe_ID` INT NOT NULL,
PRIMARY KEY (`Ingredient_ID`, `Recipe_ID`),
INDEX `fk_RecipeIngredients_Ingredients2_idx` (`Ingredient_ID` ASC),
INDEX `fk_RecipeIngredients_Recipes1_idx` (`Recipe_ID` ASC),
CONSTRAINT `fk_RecipeIngredients_Ingredients2`
FOREIGN KEY (`Ingredient_ID`)
REFERENCES `Ingredients` (`Ingredient_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_RecipeIngredients_Recipes1`
FOREIGN KEY (`Recipe_ID`)
REFERENCES `Recipes` (`Recipe_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; I have created a lot of tables and I am now trying to forward engineer my logical model into a physical database, but I am having trouble when it keeps saying "Can't add a foreign key constraint".
However, I know that the error is related to the Products referencing the Recipe_ID, but when I click on the relationship line from Recipes table to RecipeIngredients table, I see that the FK Recipe_ID IS REFERENCING the Recipe_ID primary key column in the Recipes table, not the Product table.
If that is the case, why is problem occurring? Any suggestions except recreating all of the tables again. Here is my picture model and picture of my problem.
