SQL

CREATE TABLE `scores`  (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `matchday_id` integer NOT NULL,
  `manager_id` integer NOT NULL,
  `player_id` integer NOT NULL,
  `base_points` real DEFAULT 0 NOT NULL,
  `bonus_points` real DEFAULT 0 NOT NULL,
  `total_points` real DEFAULT 0 NOT NULL,
  `breakdown` text,
  `calculated_at` text DEFAULT (datetime('now')),
  FOREIGN KEY (`matchday_id`) REFERENCES `matchdays`(`id`) ON UPDATE no action ON DELETE no action,
  FOREIGN KEY (`manager_id`) REFERENCES `managers`(`id`) ON UPDATE no action ON DELETE no action,
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON UPDATE no action ON DELETE no action
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ Rename | Drop
matchday_id INTEGER Rename | Drop
manager_id INTEGER Rename | Drop
player_id INTEGER Rename | Drop
base_points REAL Rename | Drop
bonus_points REAL Rename | Drop
total_points REAL Rename | Drop
breakdown TEXT ✓ Rename | Drop
calculated_at TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
player_id players.id
manager_id managers.id
matchday_id matchdays.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_score_matchday matchday_id SQL 
CREATE INDEX `idx_score_matchday`
ON `scores` (`matchday_id`)
Drop
uq_score
  • matchday_id
  • manager_id
  • player_id
 ✓ SQL 
CREATE UNIQUE INDEX `uq_score`
ON `scores` (`matchday_id`,`manager_id`,`player_id`)
Drop