SQL

CREATE TABLE `team_selections`  (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `matchday_id` integer NOT NULL,
  `manager_id` integer NOT NULL,
  `player_id` integer NOT NULL,
  `position` text NOT NULL,
  `bid_amount` real DEFAULT 0 NOT NULL,
  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
position TEXT Rename | Drop
bid_amount REAL 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_sel_matchday matchday_id SQL 
CREATE INDEX `idx_sel_matchday`
ON `team_selections` (`matchday_id`)
Drop
uq_selection
  • matchday_id
  • manager_id
  • player_id
 ✓ SQL 
CREATE UNIQUE INDEX `uq_selection`
ON `team_selections` (`matchday_id`,`manager_id`,`player_id`)
Drop