· 6 years ago · Dec 26, 2019, 06:16 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 PRIMARY KEY (`idRecipe`),
89 INDEX `fk_Recipe_Category1_idx` (`Category_idCategory` ASC),
90 CONSTRAINT `fk_Recipe_Category1`
91 FOREIGN KEY (`Category_idCategory`)
92 REFERENCES `mydb`.`Category` (`idCategory`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `mydb`.`Ingredient`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `mydb`.`Ingredient` (
102 `idIngredient` INT NOT NULL AUTO_INCREMENT,
103 `name` VARCHAR(45) NULL,
104 PRIMARY KEY (`idIngredient`))
105ENGINE = InnoDB;
106
107
108-- -----------------------------------------------------
109-- Table `mydb`.`contains`
110-- -----------------------------------------------------
111CREATE TABLE IF NOT EXISTS `mydb`.`contains` (
112 `idContains` VARCHAR(45) NOT NULL,
113 `Recipe_idRecipe` INT NOT NULL,
114 `Ingredient_idIngredient` INT NOT NULL,
115 `amount` VARCHAR(45) NULL,
116 INDEX `fk_Recipe_has_Ingredient_Ingredient1_idx` (`Ingredient_idIngredient` ASC),
117 INDEX `fk_Recipe_has_Ingredient_Recipe1_idx` (`Recipe_idRecipe` ASC),
118 PRIMARY KEY (`idContains`),
119 CONSTRAINT `fk_Recipe_has_Ingredient_Recipe1`
120 FOREIGN KEY (`Recipe_idRecipe`)
121 REFERENCES `mydb`.`Recipe` (`idRecipe`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION,
124 CONSTRAINT `fk_Recipe_has_Ingredient_Ingredient1`
125 FOREIGN KEY (`Ingredient_idIngredient`)
126 REFERENCES `mydb`.`Ingredient` (`idIngredient`)
127 ON DELETE NO ACTION
128 ON UPDATE NO ACTION)
129ENGINE = InnoDB;
130
131
132-- -----------------------------------------------------
133-- Table `mydb`.`post`
134-- -----------------------------------------------------
135CREATE TABLE IF NOT EXISTS `mydb`.`post` (
136 `Recipe_idRecipe` INT NOT NULL,
137 `User_idUser` INT NOT NULL,
138 PRIMARY KEY (`Recipe_idRecipe`, `User_idUser`),
139 INDEX `fk_Recipe_has_User_User1_idx` (`User_idUser` ASC),
140 INDEX `fk_Recipe_has_User_Recipe1_idx` (`Recipe_idRecipe` ASC),
141 CONSTRAINT `fk_Recipe_has_User_Recipe1`
142 FOREIGN KEY (`Recipe_idRecipe`)
143 REFERENCES `mydb`.`Recipe` (`idRecipe`)
144 ON DELETE NO ACTION
145 ON UPDATE NO ACTION,
146 CONSTRAINT `fk_Recipe_has_User_User1`
147 FOREIGN KEY (`User_idUser`)
148 REFERENCES `mydb`.`User` (`idUser`)
149 ON DELETE NO ACTION
150 ON UPDATE NO ACTION)
151ENGINE = InnoDB;
152
153
154-- -----------------------------------------------------
155-- Table `mydb`.`Picture`
156-- -----------------------------------------------------
157CREATE TABLE IF NOT EXISTS `mydb`.`Picture` (
158 `idPicture` INT NOT NULL AUTO_INCREMENT,
159 `path` VARCHAR(100) NOT NULL,
160 `Recipe_idRecipe` INT NOT NULL,
161 PRIMARY KEY (`idPicture`),
162 INDEX `fk_Picture_Recipe1_idx` (`Recipe_idRecipe` ASC),
163 CONSTRAINT `fk_Picture_Recipe1`
164 FOREIGN KEY (`Recipe_idRecipe`)
165 REFERENCES `mydb`.`Recipe` (`idRecipe`)
166 ON DELETE NO ACTION
167 ON UPDATE NO ACTION)
168ENGINE = InnoDB;
169
170
171-- -----------------------------------------------------
172-- Table `mydb`.`Favourite_category`
173-- -----------------------------------------------------
174CREATE TABLE IF NOT EXISTS `mydb`.`Favourite_category` (
175 `idFavourite_category` INT NOT NULL AUTO_INCREMENT,
176 `User_idUser` INT NOT NULL,
177 `name` VARCHAR(45) NULL,
178 PRIMARY KEY (`idFavourite_category`, `User_idUser`),
179 INDEX `fk_Favourite_category_User1_idx` (`User_idUser` ASC),
180 CONSTRAINT `fk_Favourite_category_User1`
181 FOREIGN KEY (`User_idUser`)
182 REFERENCES `mydb`.`User` (`idUser`)
183 ON DELETE NO ACTION
184 ON UPDATE NO ACTION)
185ENGINE = InnoDB;
186
187
188-- -----------------------------------------------------
189-- Table `mydb`.`exist_in`
190-- -----------------------------------------------------
191CREATE TABLE IF NOT EXISTS `mydb`.`exist_in` (
192 `Recipe_idRecipe` INT NOT NULL,
193 `Favourite_category_idFavourite_category` INT NOT NULL,
194 PRIMARY KEY (`Recipe_idRecipe`, `Favourite_category_idFavourite_category`),
195 INDEX `fk_Recipe_has_Favourite_category_Favourite_category1_idx` (`Favourite_category_idFavourite_category` ASC),
196 INDEX `fk_Recipe_has_Favourite_category_Recipe1_idx` (`Recipe_idRecipe` ASC),
197 CONSTRAINT `fk_Recipe_has_Favourite_category_Recipe1`
198 FOREIGN KEY (`Recipe_idRecipe`)
199 REFERENCES `mydb`.`Recipe` (`idRecipe`)
200 ON DELETE NO ACTION
201 ON UPDATE NO ACTION,
202 CONSTRAINT `fk_Recipe_has_Favourite_category_Favourite_category1`
203 FOREIGN KEY (`Favourite_category_idFavourite_category`)
204 REFERENCES `mydb`.`Favourite_category` (`idFavourite_category`)
205 ON DELETE NO ACTION
206 ON UPDATE NO ACTION)
207ENGINE = InnoDB;
208
209
210-- -----------------------------------------------------
211-- Table `mydb`.`Message`
212-- -----------------------------------------------------
213CREATE TABLE IF NOT EXISTS `mydb`.`Message` (
214 `idMessage` INT NOT NULL AUTO_INCREMENT,
215 `User_idUser` INT NOT NULL,
216 `User_idUser1` INT NOT NULL,
217 `date` DATETIME NULL,
218 `content` VARCHAR(150) NULL,
219 PRIMARY KEY (`idMessage`),
220 INDEX `fk_Message_User1_idx` (`User_idUser` ASC),
221 INDEX `fk_Message_User2_idx` (`User_idUser1` ASC),
222 CONSTRAINT `fk_Message_User1`
223 FOREIGN KEY (`User_idUser`)
224 REFERENCES `mydb`.`User` (`idUser`)
225 ON DELETE NO ACTION
226 ON UPDATE NO ACTION,
227 CONSTRAINT `fk_Message_User2`
228 FOREIGN KEY (`User_idUser1`)
229 REFERENCES `mydb`.`User` (`idUser`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION)
232ENGINE = InnoDB;
233
234
235SET SQL_MODE=@OLD_SQL_MODE;
236SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
237SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;