SQaPaZ8y

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`categories`
19-- -----------------------------------------------------
20DROP TABLE IF EXISTS `mydb`.`categories` ;
21
22CREATE TABLE IF NOT EXISTS `mydb`.`categories` (
23  `idFoodCategory` INT(11) NOT NULL AUTO_INCREMENT,
24  `FoodCategory` VARCHAR(45) NOT NULL,
25  PRIMARY KEY (`idFoodCategory`),
26  UNIQUE INDEX `FoodCategory_UNIQUE` (`FoodCategory` ASC))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = utf8;
29
30
31-- -----------------------------------------------------
32-- Table `mydb`.`ingredients`
33-- -----------------------------------------------------
34DROP TABLE IF EXISTS `mydb`.`ingredients` ;
35
36CREATE TABLE IF NOT EXISTS `mydb`.`ingredients` (
37  `idIngredient` INT(11) NOT NULL AUTO_INCREMENT,
38  `ingredientName` VARCHAR(45) NOT NULL,
39  PRIMARY KEY (`idIngredient`),
40  UNIQUE INDEX `ingredientName_UNIQUE` (`ingredientName` ASC))
41ENGINE = InnoDB
42AUTO_INCREMENT = 28
43DEFAULT CHARACTER SET = utf8;
44
45
46-- -----------------------------------------------------
47-- Table `mydb`.`recipe`
48-- -----------------------------------------------------
49DROP TABLE IF EXISTS `mydb`.`recipe` ;
50
51CREATE TABLE IF NOT EXISTS `mydb`.`recipe` (
52  `idRecipe` INT(11) NOT NULL AUTO_INCREMENT,
53  `recipeName` VARCHAR(45) NOT NULL,
54  `recipeDescription` TEXT NOT NULL,
55  `CookTime` INT(11) NOT NULL,
56  `PreparationTime` INT(11) NOT NULL,
57  `ServingsAmount` INT(11) NOT NULL,
58  `Image` BLOB NULL DEFAULT NULL,
59  `Rating` TINYINT(4) NULL DEFAULT NULL,
60  PRIMARY KEY (`idRecipe`))
61ENGINE = InnoDB
62AUTO_INCREMENT = 2
63DEFAULT CHARACTER SET = utf8;
64
65
66-- -----------------------------------------------------
67-- Table `mydb`.`preparationmethod`
68-- -----------------------------------------------------
69DROP TABLE IF EXISTS `mydb`.`preparationmethod` ;
70
71CREATE TABLE IF NOT EXISTS `mydb`.`preparationmethod` (
72  `idPreparationMethod` INT(11) NOT NULL AUTO_INCREMENT,
73  `Step` TINYINT(4) NOT NULL,
74  `Time` SMALLINT(6) NOT NULL,
75  `Instruction` TEXT NOT NULL,
76  `fk_idRecipe` INT(11) NOT NULL,
77  PRIMARY KEY (`idPreparationMethod`),
78  INDEX `idrecipe_idx` (`fk_idRecipe` ASC),
79  CONSTRAINT `fk_idrecipe`
80    FOREIGN KEY (`fk_idRecipe`)
81    REFERENCES `mydb`.`recipe` (`idRecipe`)
82    ON DELETE NO ACTION
83    ON UPDATE NO ACTION)
84ENGINE = InnoDB
85DEFAULT CHARACTER SET = utf8;
86
87
88-- -----------------------------------------------------
89-- Table `mydb`.`recipecategories`
90-- -----------------------------------------------------
91DROP TABLE IF EXISTS `mydb`.`recipecategories` ;
92
93CREATE TABLE IF NOT EXISTS `mydb`.`recipecategories` (
94  `idRecipeCategory` INT(11) NOT NULL AUTO_INCREMENT,
95  `fk_idFoodCategory` INT(11) NOT NULL,
96  `fk2_idRecipe` INT(11) NOT NULL,
97  PRIMARY KEY (`idRecipeCategory`),
98  INDEX `idFoodCategory_idx` (`fk_idFoodCategory` ASC),
99  INDEX `fk2_idRecipe` (`fk2_idRecipe` ASC),
100  CONSTRAINT `fk2_idRecipe`
101    FOREIGN KEY (`fk2_idRecipe`)
102    REFERENCES `mydb`.`recipe` (`idRecipe`)
103    ON DELETE NO ACTION
104    ON UPDATE NO ACTION,
105  CONSTRAINT `fk_idFoodCategory`
106    FOREIGN KEY (`fk_idFoodCategory`)
107    REFERENCES `mydb`.`categories` (`idFoodCategory`)
108    ON DELETE NO ACTION
109    ON UPDATE NO ACTION)
110ENGINE = InnoDB
111DEFAULT CHARACTER SET = utf8;
112
113
114-- -----------------------------------------------------
115-- Table `mydb`.`recipeingredients`
116-- -----------------------------------------------------
117DROP TABLE IF EXISTS `mydb`.`recipeingredients` ;
118
119CREATE TABLE IF NOT EXISTS `mydb`.`recipeingredients` (
120  `idRecipeIngredients` INT(11) NOT NULL AUTO_INCREMENT,
121  `Amount` SMALLINT(6) NOT NULL,
122  `Measurement` VARCHAR(25) NOT NULL,
123  `idRecipe` INT(11) NOT NULL,
124  `idIngredient` INT(11) NOT NULL,
125  PRIMARY KEY (`idRecipeIngredients`),
126  INDEX `idIngredients_idx` (`idIngredient` ASC),
127  INDEX `idRecipe_idx` (`idRecipe` ASC),
128  CONSTRAINT `idIngredient`
129    FOREIGN KEY (`idIngredient`)
130    REFERENCES `mydb`.`ingredients` (`idIngredient`)
131    ON DELETE NO ACTION
132    ON UPDATE NO ACTION,
133  CONSTRAINT `idRecipe`
134    FOREIGN KEY (`idRecipe`)
135    REFERENCES `mydb`.`recipe` (`idRecipe`)
136    ON DELETE NO ACTION
137    ON UPDATE NO ACTION)
138ENGINE = InnoDB
139DEFAULT CHARACTER SET = utf8;
140
141
142SET SQL_MODE=@OLD_SQL_MODE;
143SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
144SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;