Query

pilot.db — availability_exceptions

SQL

CREATE TABLE availability_exceptions  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  type TEXT NOT NULL DEFAULT 'blocked' CHECK(type IN ('blocked','extra')),
  note TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (trainer_id) REFERENCES trainers(id) ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
start_date TEXT Rename | Drop
end_date TEXT Rename | Drop
type TEXT Rename | Drop
note TEXT ✓ Rename | Drop
created_at TIMESTAMP Rename | Drop

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_exceptions_trainer
  • trainer_id
  • start_date
  • end_date
 SQL 
CREATE INDEX idx_exceptions_trainer
ON availability_exceptions(trainer_id, start_date, end_date)
Drop