diff options
Diffstat (limited to 'src/db/leagues.sql')
| -rw-r--r-- | src/db/leagues.sql | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/src/db/leagues.sql b/src/db/leagues.sql new file mode 100644 index 0000000..9407de8 --- /dev/null +++ b/src/db/leagues.sql @@ -0,0 +1,29 @@ +CREATE TABLE leagues ( + league_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + name text, + + CONSTRAINT leagues_pk PRIMARY KEY (league_id), + CONSTRAINT leagues_name_ck CHECK (char_length(name) <= 64) +); + +CREATE TABLE league_divisions ( + league_division_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + league_id bigint NOT NULL, + name text, + + CONSTRAINT league_divisions_pk PRIMARY KEY (league_division_id), + CONSTRAINT league_divisions_leagues_fk FOREIGN KEY (league_id) REFERENCES leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT league_divisions_name_ck CHECK (char_length(name) <= 64) +); + +INSERT INTO leagues (name) VALUES ('NFL') RETURNING league_id nfl_league_id \gset + +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC East'); +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC North'); +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC South'); +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC West'); + +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC East'); +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC North'); +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC South'); +INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC West'); |