~mna/hockeysim

ref: 9152d214d2eb86b42b88f1631c929f92e9f2434d hockeysim/db/migrations/009_league_teams.sql -rw-r--r-- 875 bytes View raw
9152d214Martin Angers implement league archive command 1 year, 20 days ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DROP TABLE seasons;
DROP TABLE leagues;

CREATE TABLE leagues (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name     VARCHAR(100) NOT NULL,
  state    TINYINT UNSIGNED NOT NULL DEFAULT 0, -- 0: active, 1: archived
  created  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE INDEX ix_leagues_name (name)
);

CREATE TABLE league_teams (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  league_id INT UNSIGNED NOT NULL,
  name      VARCHAR(100) NOT NULL,
  created   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE INDEX ix_league_teams_league_id_name (league_id, name),
  FOREIGN KEY (league_id) REFERENCES leagues (id) ON DELETE CASCADE
);