· 6 years ago · Dec 26, 2019, 06:30 PM
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='TRADITIONAL,ALLOW_INVALID_DATES';
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`.`Role`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Role` (
21 `idRole` INT NOT NULL AUTO_INCREMENT,
22 `name` VARCHAR(25) NOT NULL,
23 PRIMARY KEY (`idRole`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `mydb`.`User`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mydb`.`User` (
31 `idUser` INT NOT NULL AUTO_INCREMENT,
32 `username` VARCHAR(25) NOT NULL,
33 `name` VARCHAR(45) NULL,
34 `surname` VARCHAR(45) NULL,
35 `Role_idRole` INT NOT NULL,
36 PRIMARY KEY (`idUser`),
37 UNIQUE INDEX `username_UNIQUE` (`username` ASC),
38 INDEX `fk_User_Role1_idx` (`Role_idRole` ASC),
39 CONSTRAINT `fk_User_Role1`
40 FOREIGN KEY (`Role_idRole`)
41 REFERENCES `mydb`.`Role` (`idRole`)
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION)
44ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48-- Table `mydb`.`is_friend`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `mydb`.`is_friend` (
51 `id` INT NOT NULL AUTO_INCREMENT,
52 `User_idUser` INT NOT NULL,
53 `User_idUser1` INT NOT NULL,
54 `status` VARCHAR(45) NULL,
55 PRIMARY KEY (`id`),
56 INDEX `fk_User_has_User_User1_idx` (`User_idUser1` ASC),
57 INDEX `fk_User_has_User_User_idx` (`User_idUser` ASC),
58 CONSTRAINT `fk_User_has_User_User`
59 FOREIGN KEY (`User_idUser`)
60 REFERENCES `mydb`.`User` (`idUser`)
61 ON DELETE NO ACTION
62 ON UPDATE NO ACTION,
63 CONSTRAINT `fk_User_has_User_User1`
64 FOREIGN KEY (`User_idUser1`)
65 REFERENCES `mydb`.`User` (`idUser`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION)
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `mydb`.`Category`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `mydb`.`Category` (
75 `idCategory` INT NOT NULL AUTO_INCREMENT,
76 `name` VARCHAR(45) NOT NULL,
77 PRIMARY KEY (`idCategory`))
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `mydb`.`Recipe`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `mydb`.`Recipe` (
85 `idRecipe` INT NOT NULL AUTO_INCREMENT,
86 `description` VARCHAR(125) NULL,
87 `Category_idCategory` INT NOT NULL,
88 `User_idUser` INT NOT NULL,
89 PRIMARY KEY (`idRecipe`),
90 INDEX `fk_Recipe_Category1_idx` (`Category_idCategory` ASC),
91 INDEX `fk_Recipe_User1_idx` (`User_idUser` ASC),
92 CONSTRAINT `fk_Recipe_Category1`
93 FOREIGN KEY (`Category_idCategory`)
94 REFERENCES `mydb`.`Category` (`idCategory`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION,
97 CONSTRAINT `fk_Recipe_User1`
98 FOREIGN KEY (`User_idUser`)
99 REFERENCES `mydb`.`User` (`idUser`)
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `mydb`.`Ingredient`
107-- -----------------------------------------------------
108CREATE TABLE IF NOT EXISTS `mydb`.`Ingredient` (
109 `idIngredient` INT NOT NULL AUTO_INCREMENT,
110 `name` VARCHAR(45) NULL,
111 PRIMARY KEY (`idIngredient`))
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `mydb`.`contains`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `mydb`.`contains` (
119 `idContains` VARCHAR(45) NOT NULL,
120 `Recipe_idRecipe` INT NOT NULL,
121 `Ingredient_idIngredient` INT NOT NULL,
122 `amount` VARCHAR(45) NULL,
123 INDEX `fk_Recipe_has_Ingredient_Ingredient1_idx` (`Ingredient_idIngredient` ASC),
124 INDEX `fk_Recipe_has_Ingredient_Recipe1_idx` (`Recipe_idRecipe` ASC),
125 PRIMARY KEY (`idContains`),
126 CONSTRAINT `fk_Recipe_has_Ingredient_Recipe1`
127 FOREIGN KEY (`Recipe_idRecipe`)
128 REFERENCES `mydb`.`Recipe` (`idRecipe`)
129 ON DELETE NO ACTION
130 ON UPDATE NO ACTION,
131 CONSTRAINT `fk_Recipe_has_Ingredient_Ingredient1`
132 FOREIGN KEY (`Ingredient_idIngredient`)
133 REFERENCES `mydb`.`Ingredient` (`idIngredient`)
134 ON DELETE NO ACTION
135 ON UPDATE NO ACTION)
136ENGINE = InnoDB;
137
138
139-- -----------------------------------------------------
140-- Table `mydb`.`Picture`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `mydb`.`Picture` (
143 `idPicture` INT NOT NULL AUTO_INCREMENT,
144 `path` VARCHAR(100) NOT NULL,
145 `Recipe_idRecipe` INT NOT NULL,
146 PRIMARY KEY (`idPicture`),
147 INDEX `fk_Picture_Recipe1_idx` (`Recipe_idRecipe` ASC),
148 CONSTRAINT `fk_Picture_Recipe1`
149 FOREIGN KEY (`Recipe_idRecipe`)
150 REFERENCES `mydb`.`Recipe` (`idRecipe`)
151 ON DELETE NO ACTION
152 ON UPDATE NO ACTION)
153ENGINE = InnoDB;
154
155
156-- -----------------------------------------------------
157-- Table `mydb`.`Favourite_category`
158-- -----------------------------------------------------
159CREATE TABLE IF NOT EXISTS `mydb`.`Favourite_category` (
160 `idFavourite_category` INT NOT NULL AUTO_INCREMENT,
161 `User_idUser` INT NOT NULL,
162 `name` VARCHAR(45) NULL,
163 PRIMARY KEY (`idFavourite_category`, `User_idUser`),
164 INDEX `fk_Favourite_category_User1_idx` (`User_idUser` ASC),
165 CONSTRAINT `fk_Favourite_category_User1`
166 FOREIGN KEY (`User_idUser`)
167 REFERENCES `mydb`.`User` (`idUser`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION)
170ENGINE = InnoDB;
171
172
173-- -----------------------------------------------------
174-- Table `mydb`.`exist_in`
175-- -----------------------------------------------------
176CREATE TABLE IF NOT EXISTS `mydb`.`exist_in` (
177 `Recipe_idRecipe` INT NOT NULL,
178 `Favourite_category_idFavourite_category` INT NOT NULL,
179 PRIMARY KEY (`Recipe_idRecipe`, `Favourite_category_idFavourite_category`),
180 INDEX `fk_Recipe_has_Favourite_category_Favourite_category1_idx` (`Favourite_category_idFavourite_category` ASC),
181 INDEX `fk_Recipe_has_Favourite_category_Recipe1_idx` (`Recipe_idRecipe` ASC),
182 CONSTRAINT `fk_Recipe_has_Favourite_category_Recipe1`
183 FOREIGN KEY (`Recipe_idRecipe`)
184 REFERENCES `mydb`.`Recipe` (`idRecipe`)
185 ON DELETE NO ACTION
186 ON UPDATE NO ACTION,
187 CONSTRAINT `fk_Recipe_has_Favourite_category_Favourite_category1`
188 FOREIGN KEY (`Favourite_category_idFavourite_category`)
189 REFERENCES `mydb`.`Favourite_category` (`idFavourite_category`)
190 ON DELETE NO ACTION
191 ON UPDATE NO ACTION)
192ENGINE = InnoDB;
193
194
195-- -----------------------------------------------------
196-- Table `mydb`.`Message`
197-- -----------------------------------------------------
198CREATE TABLE IF NOT EXISTS `mydb`.`Message` (
199 `idMessage` INT NOT NULL AUTO_INCREMENT,
200 `User_idUser` INT NOT NULL,
201 `User_idUser1` INT NOT NULL,
202 `date` DATETIME NULL,
203 `content` VARCHAR(150) NULL,
204 PRIMARY KEY (`idMessage`),
205 INDEX `fk_Message_User1_idx` (`User_idUser` ASC),
206 INDEX `fk_Message_User2_idx` (`User_idUser1` ASC),
207 CONSTRAINT `fk_Message_User1`
208 FOREIGN KEY (`User_idUser`)
209 REFERENCES `mydb`.`User` (`idUser`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION,
212 CONSTRAINT `fk_Message_User2`
213 FOREIGN KEY (`User_idUser1`)
214 REFERENCES `mydb`.`User` (`idUser`)
215 ON DELETE NO ACTION
216 ON UPDATE NO ACTION)
217ENGINE = InnoDB;
218
219
220SET SQL_MODE=@OLD_SQL_MODE;
221SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
222SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;