SQL

CREATE TABLE abilities  (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  child_id    TEXT NOT NULL REFERENCES children(id),
  ability_id  TEXT NOT NULL,
  -- language/logic/science/creativity/social/metacognition/kinesthetic
      level       INTEGER DEFAULT 1,
  xp          INTEGER DEFAULT 0,
  xp_to_next  INTEGER DEFAULT 100,
  updated_at  DATETIME DEFAULT (datetime('now')),
  UNIQUE(child_id, ability_id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
child_id TEXT Rename | Drop
ability_id TEXT Rename | Drop
level INTEGER ✓ Rename | Drop
xp INTEGER ✓ Rename | Drop
xp_to_next INTEGER ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
child_id children.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_abilities_child child_id SQL 
CREATE INDEX idx_abilities_child
ON abilities(child_id)
Drop
sqlite_autoindex_abilities_1
  • child_id
  • ability_id
 ✓ SQL
-- no sql found --
 Drop