FirstScript.sql

-- MySQL Script generated by MySQL Workbench
-- Thu Nov  5 20:54:49 2020
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Race`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Race` (
  `raceName` VARCHAR(10) NOT NULL,
  `raceId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `speed` INT UNSIGNED NOT NULL,
  `size` VARCHAR(10) NOT NULL,
  `darkvision` TINYINT NULL,
  PRIMARY KEY (`raceId`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Language`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Language` (
  `languagesId` INT NOT NULL,
  `languagesName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`languagesId`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`RaceLanguage`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`RaceLanguage` (
  `raceId` INT UNSIGNED NOT NULL,
  `language` INT NOT NULL,
  PRIMARY KEY (`raceId`, `language`),
  INDEX `race_idx` (`raceId` ASC),
  INDEX `language1_idx` (`language` ASC),
  CONSTRAINT `race`
    FOREIGN KEY (`raceId`)
    REFERENCES `mydb`.`Race` (`raceId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `language`
    FOREIGN KEY (`language`)
    REFERENCES `mydb`.`Language` (`languagesId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`RaceAbilityScore`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`RaceAbilityScore` (
  `raceId` INT UNSIGNED NOT NULL,
  `abilityScore` VARCHAR(15) NOT NULL,
  `scoreIncrease` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`raceId`, `abilityScore`),
  CONSTRAINT `raceId`
    FOREIGN KEY (`raceId`)
    REFERENCES `mydb`.`Race` (`raceId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Proficiency`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Proficiency` (
  `proficiencyId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `proficiencyName` VARCHAR(45) NOT NULL,
  `proficiencyType` VARCHAR(45) NOT NULL COMMENT 'Skill, tool, instrument etc',
  PRIMARY KEY (`proficiencyId`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`RaceProficiency`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`RaceProficiency` (
  `raceId` INT UNSIGNED NOT NULL,
  `proficiencyId` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`raceId`, `proficiencyId`),
  INDEX `proficiencyId_idx` (`proficiencyId` ASC),
  CONSTRAINT `raceId`
    FOREIGN KEY (`raceId`)
    REFERENCES `mydb`.`Race` (`raceId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `proficiencyId`
    FOREIGN KEY (`proficiencyId`)
    REFERENCES `mydb`.`Proficiency` (`proficiencyId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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