Query

billing.db — agents

SQL

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

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
username TEXT Rename | Drop
name TEXT Rename | Drop
phone TEXT Rename | Drop
email TEXT ✓ Rename | Drop
password 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

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_agents_phone phone SQL 
CREATE INDEX idx_agents_phone
ON agents(phone)
Drop
idx_agents_status status SQL 
CREATE INDEX idx_agents_status
ON agents(status)
Drop
idx_agents_username username SQL 
CREATE INDEX idx_agents_username
ON agents(username)
Drop
sqlite_autoindex_agents_1 username ✓ SQL
-- no sql found --
 Drop
sqlite_autoindex_agents_2 phone ✓ SQL
-- no sql found --
 Drop

Triggers

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