Query

billing.db — collectors

SQL

CREATE TABLE collectors  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  phone TEXT UNIQUE NOT NULL,
  email TEXT,
  address TEXT,
  status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'suspended')),
  commission_rate DECIMAL(5,2) DEFAULT 5.00,
  -- Commission percentage
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  password TEXT
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
name TEXT Rename | Drop
phone TEXT Rename | Drop
email TEXT ✓ Rename | Drop
address TEXT ✓ Rename | Drop
status TEXT ✓ Rename | Drop
commission_rate DECIMAL(5,2) ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop
password TEXT ✓ Rename | Drop

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_collectors_password password SQL 
CREATE INDEX idx_collectors_password
ON collectors(password)
Drop
idx_collectors_phone phone SQL 
CREATE INDEX idx_collectors_phone
ON collectors(phone)
Drop
idx_collectors_status status SQL 
CREATE INDEX idx_collectors_status
ON collectors(status)
Drop
sqlite_autoindex_collectors_1 phone ✓ SQL
-- no sql found --
 Drop

Triggers

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