NZ3e5Mn9

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7-- -----------------------------------------------------
8-- Schema cdio
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema cdio
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `cdio` DEFAULT CHARACTER SET utf8 ;
15USE `cdio` ;
16
17-- -----------------------------------------------------
18-- Table `cdio`.`User`
19-- -----------------------------------------------------
20DROP TABLE IF EXISTS `cdio`.`User` ;
21
22CREATE TABLE IF NOT EXISTS `cdio`.`User` (
23  `UserID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
24  `Username` VARCHAR(45) NOT NULL,
25  `Password` VARCHAR(45) NOT NULL,
26  `Roles` TINYINT NULL DEFAULT 0,
27  UNIQUE INDEX `UserID_UNIQUE` (`UserID` ASC) VISIBLE,
28  PRIMARY KEY (`UserID`))
29ENGINE = InnoDB;
30Alter table cdio.user Auto_INCREMENT=100;
31
32
33-- -----------------------------------------------------
34-- Table `cdio`.`Character`
35-- -----------------------------------------------------
36DROP TABLE IF EXISTS `cdio`.`Character` ;
37
38CREATE TABLE IF NOT EXISTS `cdio`.`Character` (
39  `CharacterID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
40  `CName` VARCHAR(45) NOT NULL,
41  `Location` VARCHAR(45) NULL DEFAULT "Frontier City",
42  `Strength` TINYINT UNSIGNED NULL,
43  `BonusCapacity` INT NULL,
44  PRIMARY KEY (`CharacterID`),
45  UNIQUE INDEX `CharacterID_UNIQUE` (`CharacterID` ASC) VISIBLE)
46ENGINE = InnoDB;
47Alter table cdio.character AUTO_INCREMENT=1000;
48
49
50-- -----------------------------------------------------
51-- Table `cdio`.`Item`
52-- -----------------------------------------------------
53DROP TABLE IF EXISTS `cdio`.`Item` ;
54
55CREATE TABLE IF NOT EXISTS `cdio`.`Item` (
56  `ItemID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
57  `ItemName` VARCHAR(45) NOT NULL,
58  `Weight` DOUBLE UNSIGNED NULL DEFAULT 0,
59  `Description` TEXT NULL,
60  PRIMARY KEY (`ItemID`),
61  UNIQUE INDEX `ItemID_UNIQUE` (`ItemID` ASC) VISIBLE)
62ENGINE = InnoDB;
63alter table cdio.item AUTO_INCREMENT=1000;
64
65
66-- -----------------------------------------------------
67-- Table `cdio`.`Group`
68-- -----------------------------------------------------
69DROP TABLE IF EXISTS `cdio`.`Group` ;
70
71CREATE TABLE IF NOT EXISTS `cdio`.`Group` (
72  `GroupID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
73  `GroupName` VARCHAR(45) NOT NULL,
74  `Description` TEXT NULL,
75  PRIMARY KEY (`GroupID`),
76  UNIQUE INDEX `GroupID_UNIQUE` (`GroupID` ASC) VISIBLE)
77ENGINE = InnoDB;
78alter table cdio.group AUTO_INCREMENT=100;
79
80
81-- -----------------------------------------------------
82-- Table `cdio`.`CharacterRelation`
83-- -----------------------------------------------------
84DROP TABLE IF EXISTS `cdio`.`CharacterRelation` ;
85
86CREATE TABLE IF NOT EXISTS `cdio`.`CharacterRelation` (
87  `CharacterID` INT UNSIGNED NOT NULL,
88  `UserID` INT UNSIGNED NOT NULL,
89  PRIMARY KEY (`CharacterID`),
90  INDEX `UserID_idx` (`UserID` ASC) VISIBLE,
91  CONSTRAINT `CharacterID1`
92    FOREIGN KEY (`CharacterID`)
93    REFERENCES `cdio`.`Character` (`CharacterID`)
94    ON DELETE NO ACTION
95    ON UPDATE NO ACTION,
96  CONSTRAINT `UserID`
97    FOREIGN KEY (`UserID`)
98    REFERENCES `cdio`.`User` (`UserID`)
99    ON DELETE NO ACTION
100    ON UPDATE NO ACTION)
101ENGINE = InnoDB;
102
103
104-- -----------------------------------------------------
105-- Table `cdio`.`ItemRelation`
106-- -----------------------------------------------------
107DROP TABLE IF EXISTS `cdio`.`ItemRelation` ;
108
109CREATE TABLE IF NOT EXISTS `cdio`.`ItemRelation` (
110  `ItemID` INT UNSIGNED NOT NULL,
111  `CharacterID` INT UNSIGNED NOT NULL,
112  `Amount` INT UNSIGNED NOT NULL DEFAULT 1,
113  PRIMARY KEY (`ItemID`, `CharacterID`),
114  INDEX `CharacterID_idx` (`CharacterID` ASC) VISIBLE,
115  CONSTRAINT `ItemID`
116    FOREIGN KEY (`ItemID`)
117    REFERENCES `cdio`.`Item` (`ItemID`)
118    ON DELETE NO ACTION
119    ON UPDATE NO ACTION,
120  CONSTRAINT `CharacterID3`
121    FOREIGN KEY (`CharacterID`)
122    REFERENCES `cdio`.`Character` (`CharacterID`)
123    ON DELETE NO ACTION
124    ON UPDATE NO ACTION)
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `cdio`.`GroupRelation`
130-- -----------------------------------------------------
131DROP TABLE IF EXISTS `cdio`.`GroupRelation` ;
132
133CREATE TABLE IF NOT EXISTS `cdio`.`GroupRelation` (
134  `GroupID` INT UNSIGNED NOT NULL,
135  `CharacterID` INT UNSIGNED NOT NULL,
136  PRIMARY KEY (`GroupID`, `CharacterID`),
137  INDEX `CharacterID_idx` (`CharacterID` ASC) VISIBLE,
138  CONSTRAINT `GroupID`
139    FOREIGN KEY (`GroupID`)
140    REFERENCES `cdio`.`Group` (`GroupID`)
141    ON DELETE NO ACTION
142    ON UPDATE NO ACTION,
143  CONSTRAINT `CharacterID2`
144    FOREIGN KEY (`CharacterID`)
145    REFERENCES `cdio`.`Character` (`CharacterID`)
146    ON DELETE NO ACTION
147    ON UPDATE NO ACTION)
148ENGINE = InnoDB;
149
150
151SET SQL_MODE=@OLD_SQL_MODE;
152SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
153SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;