SQL

CREATE TABLE reports  (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  child_id    TEXT NOT NULL REFERENCES children(id),
  type        TEXT NOT NULL CHECK(type IN ('weekly','daily')),
  start_date  TEXT NOT NULL,
  end_date    TEXT NOT NULL,
  data_json   TEXT NOT NULL,
  created_at  DATETIME DEFAULT (datetime('now'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
child_id TEXT Rename | Drop
type TEXT Rename | Drop
start_date TEXT Rename | Drop
end_date TEXT Rename | Drop
data_json TEXT Rename | Drop
created_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
child_id children.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_reports_child
  • child_id
  • type
  • start_date
 SQL 
CREATE INDEX idx_reports_child
ON reports(child_id, type, start_date)
Drop