5jQEGp62

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `mydb` ;
7
8-- -----------------------------------------------------
9-- Table `mydb`.`users`
10-- -----------------------------------------------------
11CREATE  TABLE IF NOT EXISTS `mydb`.`users` (
12  `idusers` INT NOT NULL ,
13  `usergroup` INT NULL ,
14  `email` VARCHAR(45) NULL ,
15  `passwordhash` VARCHAR(45) NULL ,
16  `name` VARCHAR(45) NULL ,
17  `dateofbirth` VARCHAR(45) NULL ,
18  PRIMARY KEY (`idusers`) ,
19  UNIQUE INDEX `email_UNIQUE` (`email` ASC) )
20ENGINE = InnoDB;
21
22
23-- -----------------------------------------------------
24-- Table `mydb`.`leagues`
25-- -----------------------------------------------------
26CREATE  TABLE IF NOT EXISTS `mydb`.`leagues` (
27  `idleagues` INT NOT NULL ,
28  `leaguename` VARCHAR(45) NULL ,
29  PRIMARY KEY (`idleagues`) )
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `mydb`.`teams`
35-- -----------------------------------------------------
36CREATE  TABLE IF NOT EXISTS `mydb`.`teams` (
37  `idteams` INT NOT NULL ,
38  `leagues_idleagues` INT NOT NULL ,
39  `teamname` VARCHAR(45) NULL ,
40  PRIMARY KEY (`idteams`, `leagues_idleagues`) ,
41  INDEX `fk_teams_leagues1` (`leagues_idleagues` ASC) ,
42  CONSTRAINT `fk_teams_leagues1`
43    FOREIGN KEY (`leagues_idleagues` )
44    REFERENCES `mydb`.`leagues` (`idleagues` )
45    ON DELETE NO ACTION
46    ON UPDATE NO ACTION)
47ENGINE = InnoDB;
48
49
50-- -----------------------------------------------------
51-- Table `mydb`.`fixtures`
52-- -----------------------------------------------------
53CREATE  TABLE IF NOT EXISTS `mydb`.`fixtures` (
54  `idfixtures` INT NOT NULL ,
55  `leagues_idleagues` INT NOT NULL ,
56  `date` DATETIME NULL ,
57  `location` VARCHAR(45) NULL ,
58  `finalscore` VARCHAR(45) NULL DEFAULT NULL ,
59  `team1score` VARCHAR(45) NULL ,
60  `team2score` VARCHAR(45) NULL ,
61  PRIMARY KEY (`idfixtures`, `leagues_idleagues`) ,
62  INDEX `fk_fixtures_leagues1` (`leagues_idleagues` ASC) ,
63  CONSTRAINT `fk_fixtures_leagues1`
64    FOREIGN KEY (`leagues_idleagues` )
65    REFERENCES `mydb`.`leagues` (`idleagues` )
66    ON DELETE NO ACTION
67    ON UPDATE NO ACTION)
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `mydb`.`users_has_teams`
73-- -----------------------------------------------------
74CREATE  TABLE IF NOT EXISTS `mydb`.`users_has_teams` (
75  `users_idusers` INT NOT NULL ,
76  `teams_idteams` INT NOT NULL ,
77  PRIMARY KEY (`users_idusers`, `teams_idteams`) ,
78  INDEX `fk_users_has_teams_teams1` (`teams_idteams` ASC) ,
79  INDEX `fk_users_has_teams_users1` (`users_idusers` ASC) ,
80  CONSTRAINT `fk_users_has_teams_users1`
81    FOREIGN KEY (`users_idusers` )
82    REFERENCES `mydb`.`users` (`idusers` )
83    ON DELETE NO ACTION
84    ON UPDATE NO ACTION,
85  CONSTRAINT `fk_users_has_teams_teams1`
86    FOREIGN KEY (`teams_idteams` )
87    REFERENCES `mydb`.`teams` (`idteams` )
88    ON DELETE NO ACTION
89    ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `mydb`.`teams_has_fixtures`
95-- -----------------------------------------------------
96CREATE  TABLE IF NOT EXISTS `mydb`.`teams_has_fixtures` (
97  `teams_idteams` INT NOT NULL ,
98  `fixtures_idfixtures` INT NOT NULL ,
99  `fixtures_leagues_idleagues` INT NOT NULL ,
100  PRIMARY KEY (`teams_idteams`, `fixtures_idfixtures`, `fixtures_leagues_idleagues`) ,
101  INDEX `fk_teams_has_fixtures_fixtures1` (`fixtures_idfixtures` ASC, `fixtures_leagues_idleagues` ASC) ,
102  INDEX `fk_teams_has_fixtures_teams1` (`teams_idteams` ASC) ,
103  CONSTRAINT `fk_teams_has_fixtures_teams1`
104    FOREIGN KEY (`teams_idteams` )
105    REFERENCES `mydb`.`teams` (`idteams` )
106    ON DELETE NO ACTION
107    ON UPDATE NO ACTION,
108  CONSTRAINT `fk_teams_has_fixtures_fixtures1`
109    FOREIGN KEY (`fixtures_idfixtures` , `fixtures_leagues_idleagues` )
110    REFERENCES `mydb`.`fixtures` (`idfixtures` , `leagues_idleagues` )
111    ON DELETE NO ACTION
112    ON UPDATE NO ACTION)
113ENGINE = InnoDB;
114
115
116
117SET SQL_MODE=@OLD_SQL_MODE;
118SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
119SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;