Query

billing.db — cable_routes

SQL

CREATE TABLE cable_routes  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  odp_id INTEGER NOT NULL,
  cable_length DECIMAL(8,2),
  cable_type VARCHAR(50) DEFAULT 'Fiber Optic',
  installation_date DATE,
  status VARCHAR(20) DEFAULT 'connected' CHECK (status IN ('connected', 'disconnected', 'maintenance', 'damaged')),
  port_number INTEGER,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (odp_id) REFERENCES odps(id) ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
customer_id INTEGER Rename | Drop
odp_id INTEGER Rename | Drop
cable_length DECIMAL(8,2) ✓ Rename | Drop
cable_type VARCHAR(50) ✓ Rename | Drop
installation_date DATE ✓ Rename | Drop
status VARCHAR(20) ✓ Rename | Drop
port_number INTEGER ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
odp_id odps.id
customer_id customers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_cable_routes_customer customer_id SQL 
CREATE INDEX idx_cable_routes_customer
ON cable_routes(customer_id)
Drop
idx_cable_routes_odp odp_id SQL 
CREATE INDEX idx_cable_routes_odp
ON cable_routes(odp_id)
Drop
idx_cable_routes_status status SQL 
CREATE INDEX idx_cable_routes_status
ON cable_routes(status)
Drop

Triggers

Name SQL Drop?
update_cable_routes_updated_at SQL 
CREATE TRIGGER update_cable_routes_updated_at 
                AFTER UPDATE ON cable_routes
                FOR EACH ROW
            BEGIN
                UPDATE cable_routes SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
            END
Drop
update_odp_used_ports_insert SQL 
CREATE TRIGGER update_odp_used_ports_insert
                AFTER INSERT ON cable_routes
                FOR EACH ROW
            BEGIN
                UPDATE odps SET used_ports = used_ports + 1 WHERE id = NEW.odp_id;
            END
Drop
update_odp_used_ports_delete SQL 
CREATE TRIGGER update_odp_used_ports_delete
                AFTER DELETE ON cable_routes
                FOR EACH ROW
            BEGIN
                UPDATE odps SET used_ports = used_ports - 1 WHERE id = OLD.odp_id;
            END
Drop
update_odp_used_ports_change SQL 
CREATE TRIGGER update_odp_used_ports_change
                AFTER UPDATE OF odp_id ON cable_routes
                FOR EACH ROW
                WHEN NEW.odp_id IS NOT OLD.odp_id
            BEGIN
                UPDATE odps SET used_ports = used_ports - 1 WHERE id = OLD.odp_id;
                UPDATE odps SET used_ports = used_ports + 1 WHERE id = NEW.odp_id;
            END
Drop