SQLScript2.sql

-- MySQL Script generated by MySQL Workbench
-- Tue Dec 31 13:33:27 2019
-- 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 BuginatorDB
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `BuginatorDB`.`Role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `BuginatorDB`.`Role` ;

CREATE TABLE IF NOT EXISTS `BuginatorDB`.`Role` (
  `RoleId` CHAR(36) NOT NULL,
  `RoleName` NVARCHAR(60) NOT NULL,
  PRIMARY KEY (`RoleId`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BuginatorDB`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `BuginatorDB`.`User` ;

CREATE TABLE IF NOT EXISTS `BuginatorDB`.`User` (
  `UserId` CHAR(36) NOT NULL,
  `Name` NVARCHAR(60) NOT NULL,
  `Age` INT NULL,
  `DateOfCreation` DATETIME NOT NULL,
  `Email` NVARCHAR(100) NULL,
  `Note` NVARCHAR(255) NULL,
  `Role_RoleId` CHAR(36) NULL,
  PRIMARY KEY (`UserId`),
  INDEX `fk_User_Role_idx` (`Role_RoleId` ASC) VISIBLE,
  CONSTRAINT `fk_User_Role`
    FOREIGN KEY (`Role_RoleId`)
    REFERENCES `BuginatorDB`.`Role` (`RoleId`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BuginatorDB`.`Project`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `BuginatorDB`.`Project` ;

CREATE TABLE IF NOT EXISTS `BuginatorDB`.`Project` (
  `ProjectId` CHAR(36) NOT NULL,
  `Title` NVARCHAR(255) NOT NULL,
  `Description` NVARCHAR(255) NULL,
  `DateOfCreation` DATETIME NOT NULL,
  PRIMARY KEY (`ProjectId`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BuginatorDB`.`Ticket`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `BuginatorDB`.`Ticket` ;

CREATE TABLE IF NOT EXISTS `BuginatorDB`.`Ticket` (
  `TicketId` CHAR(36) NOT NULL,
  `Description` NVARCHAR(255) NULL,
  `DateOfCreation` DATETIME NOT NULL,
  `Title` NVARCHAR(255) NOT NULL,
  `Project_ProjectId` CHAR(36) NOT NULL,
  PRIMARY KEY (`TicketId`),
  INDEX `fk_Ticket_Project1_idx` (`Project_ProjectId` ASC) VISIBLE,
  CONSTRAINT `fk_Ticket_Project1`
    FOREIGN KEY (`Project_ProjectId`)
    REFERENCES `BuginatorDB`.`Project` (`ProjectId`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BuginatorDB`.`User_has_Ticket`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `BuginatorDB`.`User_has_Ticket` ;

CREATE TABLE IF NOT EXISTS `BuginatorDB`.`User_has_Ticket` (
  `User_has_TicketId` CHAR(36) NULL,
  `UserTicketRole` NVARCHAR(45) NOT NULL,
  `User_UserId` CHAR(36) NOT NULL,
  `Ticket_TicketId` CHAR(36) NOT NULL,
  PRIMARY KEY (`User_UserId`, `Ticket_TicketId`),
  INDEX `fk_User_has_Ticket_Ticket1_idx` (`Ticket_TicketId` ASC) VISIBLE,
  INDEX `fk_User_has_Ticket_User1_idx` (`User_UserId` ASC) VISIBLE,
  CONSTRAINT `fk_User_has_Ticket_User1`
    FOREIGN KEY (`User_UserId`)
    REFERENCES `BuginatorDB`.`User` (`UserId`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_User_has_Ticket_Ticket1`
    FOREIGN KEY (`Ticket_TicketId`)
    REFERENCES `BuginatorDB`.`Ticket` (`TicketId`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BuginatorDB`.`User_has_Project`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `BuginatorDB`.`User_has_Project` ;

CREATE TABLE IF NOT EXISTS `BuginatorDB`.`User_has_Project` (
  `User_has_projectId` CHAR(36) NOT NULL,
  `User_UserId` CHAR(36) NOT NULL,
  `Project_ProjectId` CHAR(36) NOT NULL,
  PRIMARY KEY (`User_has_projectId`, `User_UserId`, `Project_ProjectId`),
  INDEX `fk_User_has_Project_Project1_idx` (`Project_ProjectId` ASC) VISIBLE,
  INDEX `fk_User_has_Project_User1_idx` (`User_UserId` ASC) VISIBLE,
  CONSTRAINT `fk_User_has_Project_User1`
    FOREIGN KEY (`User_UserId`)
    REFERENCES `BuginatorDB`.`User` (`UserId`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_User_has_Project_Project1`
    FOREIGN KEY (`Project_ProjectId`)
    REFERENCES `BuginatorDB`.`Project` (`ProjectId`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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