~mna/hockeysim

ref: 1427589f710a5c5d4e2a468c4f0e3db1ab9f7bd0 hockeysim/db/migrations/009_league_teams.sql -rw-r--r-- 854 bytes View raw
1427589fMartin Angers add length validation to new league 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_name (name),
  FOREIGN KEY (league_id) REFERENCES leagues (id) ON DELETE CASCADE
);