hp7tJc84

1-- MySQL Script generated by MySQL Workbench
2-- Sat May  4 14:40:54 2019
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @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';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13-- -----------------------------------------------------
14-- Schema project
15-- -----------------------------------------------------
16
17-- -----------------------------------------------------
18-- Schema project
19-- -----------------------------------------------------
20CREATE SCHEMA IF NOT EXISTS `project` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
21USE `project` ;
22
23-- -----------------------------------------------------
24-- Table `project`.`users`
25-- -----------------------------------------------------
26DROP TABLE IF EXISTS `project`.`users` ;
27
28CREATE TABLE IF NOT EXISTS `project`.`users` (
29  `user_id` INT(11) NOT NULL,
30  `nick_name` VARCHAR(45) NOT NULL,
31  `fname` VARCHAR(45) NOT NULL,
32  `lname` VARCHAR(45) NOT NULL,
33  `email` VARCHAR(45) NOT NULL,
34  `passwrd` VARCHAR(45) NOT NULL,
35  `admin_value` INT(11) NOT NULL,
36  `TS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
37  PRIMARY KEY (`user_id`))
38ENGINE = InnoDB
39DEFAULT CHARACTER SET = utf8mb4
40COLLATE = utf8mb4_0900_ai_ci;
41
42
43-- -----------------------------------------------------
44-- Table `project`.`pictures`
45-- -----------------------------------------------------
46DROP TABLE IF EXISTS `project`.`pictures` ;
47
48CREATE TABLE IF NOT EXISTS `project`.`pictures` (
49  `pic_id` INT(11) NOT NULL,
50  `post_id` INT(11) NOT NULL,
51  `filename+picture` BLOB NOT NULL,
52  `TS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
53  PRIMARY KEY (`pic_id`),
54  INDEX `fk_userspics` (`post_id` ASC) VISIBLE,
55  CONSTRAINT `fk_userspics`
56    FOREIGN KEY (`post_id`)
57    REFERENCES `project`.`users` (`user_id`))
58ENGINE = InnoDB
59DEFAULT CHARACTER SET = utf8mb4
60COLLATE = utf8mb4_0900_ai_ci;
61
62
63-- -----------------------------------------------------
64-- Table `project`.`posts`
65-- -----------------------------------------------------
66DROP TABLE IF EXISTS `project`.`posts` ;
67
68CREATE TABLE IF NOT EXISTS `project`.`posts` (
69  `post_id` INT(11) NOT NULL,
70  `user_id` INT(11) NOT NULL,
71  `pic_id` INT(11) NOT NULL,
72  `likes` INT(11) NOT NULL,
73  `text` VARCHAR(45) NULL DEFAULT NULL,
74  `comments` VARCHAR(45) NULL DEFAULT NULL,
75  `nick_name` VARCHAR(45) NOT NULL,
76  `TS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
77  `postlikes` INT(11) NULL DEFAULT NULL,
78  PRIMARY KEY (`post_id`),
79  INDEX `fk_USERPOSTS` (`user_id` ASC) VISIBLE,
80  INDEX `fk_likesposts` (`likes` ASC) VISIBLE,
81  INDEX `fk_picsposts` (`pic_id` ASC) VISIBLE,
82  CONSTRAINT `fk_USERPOSTS`
83    FOREIGN KEY (`user_id`)
84    REFERENCES `project`.`users` (`user_id`),
85  CONSTRAINT `fk_picsposts`
86    FOREIGN KEY (`pic_id`)
87    REFERENCES `project`.`pictures` (`pic_id`),
88  CONSTRAINT `posts_ibfk_1`
89    FOREIGN KEY (`user_id`)
90    REFERENCES `project`.`users` (`user_id`))
91ENGINE = InnoDB
92DEFAULT CHARACTER SET = utf8mb4
93COLLATE = utf8mb4_0900_ai_ci;
94
95
96SET SQL_MODE=@OLD_SQL_MODE;
97SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
98SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;