Docs/sql-statements.md at main · psionic-storm/Docs

Create Tables

-- MySQL Script generated by MySQL Workbench
-- Tue Mar 16 22:10:13 2021
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @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';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema psionic_storm
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema psionic_storm
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `psionic_storm` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `psionic_storm` ;

-- -----------------------------------------------------
-- Table `psionic_storm`.`book`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`book` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`book` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `author` VARCHAR(200) NOT NULL,
  `description` VARCHAR(2000) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `salon_id` INT NULL DEFAULT NULL,
  `space_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_book_salon1_idx` (`salon_id` ASC) VISIBLE,
  INDEX `fk_book_space1_idx` (`space_id` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`user` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `login_id` VARCHAR(45) NOT NULL,
  `nickname` VARCHAR(45) NOT NULL,
  `hashed_password` VARCHAR(200) NOT NULL,
  `salt` VARCHAR(200) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 36
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`quote`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`quote` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`quote` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `content` VARCHAR(2000) NOT NULL,
  `page` INT NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `book_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`, `book_id`, `user_id`),
  INDEX `fk_quote_book1_idx` (`book_id` ASC) VISIBLE,
  INDEX `fk_quote_user1_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_quote_book1`
    FOREIGN KEY (`book_id`)
    REFERENCES `psionic_storm`.`book` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_quote_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `psionic_storm`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 15
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`quote_comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`quote_comment` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`quote_comment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `comment` VARCHAR(2000) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `quote_id` INT NOT NULL,
  `quote_book_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`, `quote_id`, `quote_book_id`, `user_id`),
  INDEX `fk_quote_comment_quote1_idx` (`quote_id` ASC, `quote_book_id` ASC) VISIBLE,
  INDEX `fk_quote_comment_user1_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_quote_comment_quote1`
    FOREIGN KEY (`quote_id` , `quote_book_id`)
    REFERENCES `psionic_storm`.`quote` (`id` , `book_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_quote_comment_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `psionic_storm`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 16
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`review`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`review` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`review` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `content` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `book_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`, `book_id`),
  INDEX `fk_review_book1_idx` (`book_id` ASC) VISIBLE,
  INDEX `fk_review_user1_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_review_book1`
    FOREIGN KEY (`book_id`)
    REFERENCES `psionic_storm`.`book` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_review_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `psionic_storm`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`review_comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`review_comment` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`review_comment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `comment` VARCHAR(2000) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `review_id` INT NOT NULL,
  `review_book_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`, `review_id`, `review_book_id`),
  INDEX `fk_review_comment_review1_idx` (`review_id` ASC, `review_book_id` ASC) VISIBLE,
  INDEX `fk_review_comment_user1_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_review_comment_review1`
    FOREIGN KEY (`review_id` , `review_book_id`)
    REFERENCES `psionic_storm`.`review` (`id` , `book_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_review_comment_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `psionic_storm`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 12
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`salon`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`salon` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`salon` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `creator_id` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`salon_has_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`salon_has_user` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`salon_has_user` (
  `salon_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  INDEX `fk_salon_has_user_user1_idx` (`user_id` ASC) VISIBLE,
  INDEX `fk_salon_has_user_salon1_idx` (`salon_id` ASC) VISIBLE,
  CONSTRAINT `fk_salon_has_user_salon1`
    FOREIGN KEY (`salon_id`)
    REFERENCES `psionic_storm`.`salon` (`id`),
  CONSTRAINT `fk_salon_has_user_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `psionic_storm`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `psionic_storm`.`space`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `psionic_storm`.`space` ;

CREATE TABLE IF NOT EXISTS `psionic_storm`.`space` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_space_user_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_space_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `psionic_storm`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Create Tables

FilesExpand file tree

sql-statements.md

Latest commit

History

sql-statements.md

File metadata and controls

Create Tables
