CREATE TABLE machines (
machine SERIAL PRIMARY KEY,
- name VARCHAR NOT NULL
+ name VARCHAR NOT NULL,
+
+ UNIQUE ( name )
);
CREATE TABLE countries (
country SERIAL PRIMARY KEY,
- name VARCHAR NOT NULL
+ name VARCHAR NOT NULL,
+
+ UNIQUE ( name )
);
CREATE TABLE songs (
CREATE TABLE scoringsystems (
scoringsystem SERIAL PRIMARY KEY,
- name VARCHAR NOT NULL
+ name VARCHAR NOT NULL,
+
+ UNIQUE ( name )
);
CREATE TABLE songratings (
CREATE TABLE players (
player SERIAL PRIMARY KEY,
- nick VARCHAR NOT NULL
+ nick VARCHAR NOT NULL,
+
+ UNIQUE ( nick )
);
CREATE TABLE seasons (
season SERIAL PRIMARY KEY,
- name VARCHAR NOT NULL
+ name VARCHAR NOT NULL,
+
+ UNIQUE ( name )
);
CREATE TABLE tournaments (
location VARCHAR NOT NULL,
"date" DATE NOT NULL,
machine INTEGER NOT NULL REFERENCES machines,
- scoringsystem INTEGER NOT NULL REFERENCES scoringsystems
+ scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
+
+ UNIQUE ( season, name )
);
CREATE TABLE rounds (
round INTEGER NOT NULL,
parallel INTEGER NOT NULL,
player INTEGER NOT NULL REFERENCES players,
+ position INTEGER NOT NULL,
UNIQUE (tournament, round, player),
+ UNIQUE (tournament, round, parallel, position),
FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
PRIMARY KEY (tournament, round, parallel, player)
);