-
Notifications
You must be signed in to change notification settings - Fork 0
/
eskodb.sql
30 lines (26 loc) · 915 Bytes
/
eskodb.sql
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
26
27
28
29
30
CREATE TABLE courses (
id serial PRIMARY KEY,
name text NOT NULL CHECK (name <> ''),
holes integer CHECK (holes > 0),
version integer DEFAULT 0,
CONSTRAINT courses_differ UNIQUE(name, holes, version)
);
CREATE TABLE players (
name text PRIMARY KEY,
active integer REFERENCES courses(id) default NULL
);
CREATE TABLE results (
course serial REFERENCES courses(id),
player text,
hole integer,
throws integer,
penalty integer default 0,
game_date date default CURRENT_DATE,
game_of_day integer default 1,
in_play boolean default false,
PRIMARY KEY (course, player, hole, game_date, game_of_day),
FOREIGN KEY (course) REFERENCES courses (id),
FOREIGN KEY (player) REFERENCES players(name)
);
-- ALTER TABLE results ADD COLUMN in_play boolean default false;
-- ALTER TABLE players ADD COLUMN active integer REFERENCES courses(id) default NULL;