Query

billing.db — collector_assignments

SQL

CREATE TABLE collector_assignments  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  collector_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  status TEXT DEFAULT 'active' CHECK(status IN ('active', 'completed', 'cancelled')),
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (collector_id) REFERENCES collectors(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  UNIQUE(collector_id, customer_id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
collector_id INTEGER Rename | Drop
customer_id INTEGER Rename | Drop
assigned_date DATETIME ✓ Rename | Drop
status TEXT ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
customer_id customers.id
collector_id collectors.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_collector_assignments_collector_id collector_id SQL 
CREATE INDEX idx_collector_assignments_collector_id
ON collector_assignments(collector_id)
Drop
idx_collector_assignments_customer_id customer_id SQL 
CREATE INDEX idx_collector_assignments_customer_id
ON collector_assignments(customer_id)
Drop
sqlite_autoindex_collector_assignments_1
  • collector_id
  • customer_id
 ✓ SQL
-- no sql found --
 Drop

Triggers

Name SQL Drop?
update_collector_assignments_updated_at SQL 
CREATE TRIGGER update_collector_assignments_updated_at
    AFTER UPDATE ON collector_assignments
    FOR EACH ROW
BEGIN
    UPDATE collector_assignments SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END
Drop