· 9 years ago · Jan 25, 2017, 07:08 AM
1-- MySQL Script generated by MySQL Workbench
2-- Tue 24 Jan 2017 11:24:26 PM MST
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='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema blog
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema blog
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `blog` DEFAULT CHARACTER SET utf8 ;
18USE `blog` ;
19
20-- -----------------------------------------------------
21-- Table `blog`.`Users`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `blog`.`Users` (
24 `user_id` INT NOT NULL AUTO_INCREMENT,
25 `display_name` VARCHAR(45) NULL,
26 `email_addr` VARCHAR(45) NOT NULL,
27 `about_me` VARCHAR(45) NULL,
28 `last_seen` DATE NOT NULL,
29 `oath_token` VARCHAR(128) NULL,
30 PRIMARY KEY (`user_id`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `blog`.`Posts`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `blog`.`Posts` (
38 `post_id` INT NOT NULL,
39 `title` VARCHAR(45) NOT NULL,
40 `body` VARCHAR(1000) NULL,
41 `time_created` DATE NOT NULL,
42 `Users_user_id` INT NOT NULL,
43 PRIMARY KEY (`post_id`, `Users_user_id`),
44 INDEX `fk_Posts_Users_idx` (`Users_user_id` ASC),
45 CONSTRAINT `fk_Posts_Users`
46 FOREIGN KEY (`Users_user_id`)
47 REFERENCES `blog`.`Users` (`user_id`)
48 ON DELETE NO ACTION
49 ON UPDATE NO ACTION)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `blog`.`Photos`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `blog`.`Photos` (
57 `photo_id` INT NOT NULL,
58 `filesystem_location` VARCHAR(45) NOT NULL,
59 `created_on` DATE NOT NULL,
60 `Users_user_id` INT NOT NULL,
61 `Posts_post_id` INT NOT NULL,
62 PRIMARY KEY (`photo_id`, `Users_user_id`, `Posts_post_id`),
63 INDEX `fk_Photos_Users1_idx` (`Users_user_id` ASC),
64 INDEX `fk_Photos_Posts1_idx` (`Posts_post_id` ASC),
65 CONSTRAINT `fk_Photos_Users1`
66 FOREIGN KEY (`Users_user_id`)
67 REFERENCES `blog`.`Users` (`user_id`)
68 ON DELETE NO ACTION
69 ON UPDATE NO ACTION,
70 CONSTRAINT `fk_Photos_Posts1`
71 FOREIGN KEY (`Posts_post_id`)
72 REFERENCES `blog`.`Posts` (`post_id`)
73 ON DELETE NO ACTION
74 ON UPDATE NO ACTION)
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `blog`.`Comments`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `blog`.`Comments` (
82 `idComments` INT NOT NULL,
83 `body` VARCHAR(64) NOT NULL,
84 `created_on` DATE NOT NULL,
85 `Posts_post_id` INT NOT NULL,
86 `Users_user_id` INT NOT NULL,
87 PRIMARY KEY (`idComments`, `Posts_post_id`, `Users_user_id`),
88 INDEX `fk_Comments_Posts1_idx` (`Posts_post_id` ASC),
89 INDEX `fk_Comments_Users1_idx` (`Users_user_id` ASC),
90 CONSTRAINT `fk_Comments_Posts1`
91 FOREIGN KEY (`Posts_post_id`)
92 REFERENCES `blog`.`Posts` (`post_id`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION,
95 CONSTRAINT `fk_Comments_Users1`
96 FOREIGN KEY (`Users_user_id`)
97 REFERENCES `blog`.`Users` (`user_id`)
98 ON DELETE NO ACTION
99 ON UPDATE NO ACTION)
100ENGINE = InnoDB;
101
102
103-- -----------------------------------------------------
104-- Table `blog`.`Owner`
105-- -----------------------------------------------------
106CREATE TABLE IF NOT EXISTS `blog`.`Owner` (
107 `Users_user_id` INT NOT NULL,
108 PRIMARY KEY (`Users_user_id`),
109 CONSTRAINT `fk_Administrators_Users1`
110 FOREIGN KEY (`Users_user_id`)
111 REFERENCES `blog`.`Users` (`user_id`)
112 ON DELETE NO ACTION
113 ON UPDATE NO ACTION)
114ENGINE = InnoDB;
115
116
117SET SQL_MODE=@OLD_SQL_MODE;
118SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
119SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;