SQL

CREATE TABLE logistics  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type_id INTEGER NOT NULL REFERENCES logistics_types(id),
  title TEXT NOT NULL,
  description TEXT,
  responsible_id INTEGER REFERENCES users(id),
  status TEXT NOT NULL DEFAULT 'Pendiente' CHECK(status IN ('Pendiente','En curso','Completo','Cancelado')),
  start_date TEXT,
  end_date TEXT,
  created_by INTEGER REFERENCES users(id),
  updated_by INTEGER REFERENCES users(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
type_id INTEGER Rename | Drop
title TEXT Rename | Drop
description TEXT ✓ Rename | Drop
responsible_id INTEGER ✓ Rename | Drop
status TEXT Rename | Drop
start_date TEXT ✓ Rename | Drop
end_date TEXT ✓ Rename | Drop
created_by INTEGER ✓ Rename | Drop
updated_by INTEGER ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
updated_by users.id
created_by users.id
responsible_id users.id
type_id logistics_types.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_logistics_dates
  • start_date
  • end_date
 SQL 
CREATE INDEX idx_logistics_dates
ON logistics(start_date, end_date)
Drop
idx_logistics_status status SQL 
CREATE INDEX idx_logistics_status
ON logistics(status)
Drop
idx_logistics_type type_id SQL 
CREATE INDEX idx_logistics_type
ON logistics(type_id)
Drop