🎯 Advanced Updates & Deletes

Advanced data modification requires careful consideration of data integrity, relationships, and business requirements. This section covers sophisticated techniques for handling complex update and delete scenarios.

🔗 Cascading Updates and Deletes

Foreign key constraints with cascading actions automatically propagate updates and deletes through related tables.

Example 1: Cascade Configuration and Behavior

import sqlite3

# Enable foreign keys (must be done for each connection)
conn = sqlite3.connect('library.db')
conn.execute("PRAGMA foreign_keys = ON")
cursor = conn.cursor()

# Create tables with cascading delete
cursor.executescript("""
    CREATE TABLE IF NOT EXISTS authors (
        author_id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    );
    
    CREATE TABLE IF NOT EXISTS books (
        book_id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author_id INTEGER NOT NULL,
        FOREIGN KEY (author_id) 
            REFERENCES authors(author_id) 
            ON DELETE CASCADE
    );
""")

# When you delete an author, all their books are automatically deleted
cursor.execute("DELETE FROM authors WHERE author_id = 5")
conn.commit()

# Verify cascade worked
cursor.execute("SELECT COUNT(*) FROM books WHERE author_id = 5")
print(f"Books by author 5: {cursor.fetchone()[0]}")  # Should be 0

conn.close()

Example 2: Cascade Update on Foreign Keys

import sqlite3

conn = sqlite3.connect('ecommerce.db')
conn.execute("PRAGMA foreign_keys = ON")
cursor = conn.cursor()

# Create tables with cascade update
cursor.executescript("""
    CREATE TABLE IF NOT EXISTS categories (
        category_id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    );
    
    CREATE TABLE IF NOT EXISTS products (
        product_id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        category_id INTEGER NOT NULL,
        FOREIGN KEY (category_id) 
            REFERENCES categories(category_id) 
            ON UPDATE CASCADE
    );
""")

# When category_id changes, all related products are updated
cursor.execute("UPDATE categories SET category_id = 100 WHERE category_id = 5")
conn.commit()

cursor.execute("SELECT COUNT(*) FROM products WHERE category_id = 100")
print(f"Products in category 100: {cursor.fetchone()[0]}")

conn.close()

🗂️ Soft Deletes (Logical Deletes)

Instead of permanently deleting records, mark them as deleted. This preserves data history and allows recovery.

Example 3: Implementing Soft Deletes

import sqlite3
from datetime import datetime

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Add soft delete columns if not exists
cursor.execute("""
    ALTER TABLE customers 
    ADD COLUMN is_deleted BOOLEAN DEFAULT 0
""")

cursor.execute("""
    ALTER TABLE customers 
    ADD COLUMN deleted_at TIMESTAMP
""")

conn.commit()

# "Delete" a customer (soft delete)
customer_id = 42
cursor.execute("""
    UPDATE customers
    SET is_deleted = 1, deleted_at = ?
    WHERE customer_id = ?
""", (datetime.now(), customer_id))

conn.commit()
print("Customer marked as deleted (soft delete)")

# Query only active customers
cursor.execute("""
    SELECT customer_id, name, email
    FROM customers
    WHERE is_deleted = 0
    ORDER BY name
""")

active_customers = cursor.fetchall()
print(f"Active customers: {len(active_customers)}")

# Restore a deleted customer
cursor.execute("""
    UPDATE customers
    SET is_deleted = 0, deleted_at = NULL
    WHERE customer_id = ?
""", (customer_id,))

conn.commit()
print(f"Customer {customer_id} restored")

conn.close()

Example 4: Views for Automatic Filtering

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Create a view that automatically excludes deleted records
cursor.execute("""
    CREATE VIEW IF NOT EXISTS active_customers AS
    SELECT customer_id, name, email, created_at
    FROM customers
    WHERE is_deleted = 0
""")

cursor.execute("""
    CREATE VIEW IF NOT EXISTS active_orders AS
    SELECT 
        o.order_id, 
        o.customer_id,
        c.name as customer_name,
        o.order_date,
        o.total_amount
    FROM orders o
    JOIN active_customers c ON o.customer_id = c.customer_id
    WHERE o.is_deleted = 0
""")

conn.commit()

# Use the view - no need to add WHERE clauses
cursor.execute("SELECT * FROM active_customers")
customers = cursor.fetchall()
print(f"Retrieved {len(customers)} customers from view")

conn.close()

🔄 Batch Updates with Conditions

Update multiple records based on complex conditions.

Example 5: Complex Batch Updates

import sqlite3

conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

# Update product prices based on inventory levels
# Low stock items: 15% discount
cursor.execute("""
    UPDATE products
    SET price = price * 0.85,
        discount_reason = 'Low Stock Clearance'
    WHERE quantity_in_stock < 10
    AND category NOT IN ('Premium', 'Limited Edition')
""")

low_stock_updates = cursor.rowcount
print(f"Applied discount to {low_stock_updates} low-stock items")

# Seasonal items: add markup for upcoming season
cursor.execute("""
    UPDATE products
    SET price = price * 1.20,
        seasonal_markup = 1.20,
        updated_at = CURRENT_TIMESTAMP
    WHERE category = 'Winter Clothing'
    AND strftime('%m', CURRENT_TIMESTAMP) IN ('10', '11', '12')
""")

seasonal_updates = cursor.rowcount
print(f"Applied seasonal markup to {seasonal_updates} winter items")

conn.commit()
conn.close()

🚨 Transaction Safety and Rollback

Use transactions to ensure data consistency and allow rollback on errors.

Example 6: Multi-Step Transaction

import sqlite3

conn = sqlite3.connect('bank.db')
cursor = conn.cursor()

def transfer_funds(from_account, to_account, amount):
    try:
        # Begin transaction (implicit in SQLite)
        
        # Debit source account
        cursor.execute("""
            UPDATE accounts
            SET balance = balance - ?
            WHERE account_id = ? AND balance >= ?
        """, (amount, from_account, amount))
        
        if cursor.rowcount == 0:
            raise Exception("Insufficient funds or account not found")
        
        # Credit destination account
        cursor.execute("""
            UPDATE accounts
            SET balance = balance + ?
            WHERE account_id = ?
        """, (amount, to_account))
        
        if cursor.rowcount == 0:
            raise Exception("Destination account not found")
        
        # Record transaction
        cursor.execute("""
            INSERT INTO transactions (from_account, to_account, amount, timestamp)
            VALUES (?, ?, ?, CURRENT_TIMESTAMP)
        """, (from_account, to_account, amount))
        
        # All steps succeeded, commit
        conn.commit()
        print(f"Transferred ${amount:.2f} from account {from_account} to {to_account}")
        return True
        
    except Exception as e:
        # Error occurred, rollback all changes
        conn.rollback()
        print(f"Transfer failed: {e}")
        return False
    finally:
        # Check final balances
        cursor.execute("SELECT account_id, balance FROM accounts WHERE account_id IN (?, ?)",
                      (from_account, to_account))
        for account_id, balance in cursor.fetchall():
            print(f"  Account {account_id}: ${balance:.2f}")

# Test the transfer
transfer_funds(1001, 1002, 100.00)
conn.close()

🔍 Conflict Resolution Strategies

Handle conflicts when updates violate constraints.

Example 7: Using REPLACE and INSERT OR IGNORE

import sqlite3

conn = sqlite3.connect('inventory.db')
cursor = conn.cursor()

# Create table with UNIQUE constraint
cursor.execute("""
    CREATE TABLE IF NOT EXISTS inventory (
        product_id INTEGER PRIMARY KEY,
        sku TEXT UNIQUE NOT NULL,
        quantity INTEGER,
        last_updated TIMESTAMP
    )
""")

# Approach 1: REPLACE (delete and re-insert)
cursor.execute("""
    REPLACE INTO inventory (product_id, sku, quantity, last_updated)
    VALUES (1, 'SKU-001', 100, CURRENT_TIMESTAMP)
""")
print("Used REPLACE strategy")

# Approach 2: INSERT OR IGNORE (skip if exists)
cursor.execute("""
    INSERT OR IGNORE INTO inventory (product_id, sku, quantity, last_updated)
    VALUES (2, 'SKU-002', 50, CURRENT_TIMESTAMP)
""")
print("Used INSERT OR IGNORE strategy")

# Approach 3: INSERT OR REPLACE (similar to REPLACE)
cursor.execute("""
    INSERT OR REPLACE INTO inventory (product_id, sku, quantity, last_updated)
    VALUES (1, 'SKU-001', 120, CURRENT_TIMESTAMP)
""")
print("Used INSERT OR REPLACE strategy")

conn.commit()
conn.close()

🛡️ Audit Trails and History Tracking

Maintain complete history of changes for compliance and debugging.

Example 8: Audit Trail Implementation

import sqlite3
from datetime import datetime

conn = sqlite3.connect('audited_database.db')
cursor = conn.cursor()

# Create main table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        user_id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL,
        status TEXT DEFAULT 'active'
    )
""")

# Create audit table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS user_audit_log (
        log_id INTEGER PRIMARY KEY,
        user_id INTEGER,
        action TEXT,
        old_values TEXT,
        new_values TEXT,
        changed_by TEXT,
        changed_at TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(user_id)
    )
""")

# Create trigger for audit (SQLite supports triggers)
cursor.execute("""
    CREATE TRIGGER IF NOT EXISTS user_update_trigger
    AFTER UPDATE ON users
    FOR EACH ROW
    BEGIN
        INSERT INTO user_audit_log (user_id, action, old_values, new_values, changed_by, changed_at)
        VALUES (
            NEW.user_id,
            'UPDATE',
            json_object('email', OLD.email, 'status', OLD.status),
            json_object('email', NEW.email, 'status', NEW.status),
            'system',
            CURRENT_TIMESTAMP
        );
    END
""")

conn.commit()

# Update a user (trigger automatically logs it)
cursor.execute("""
    UPDATE users
    SET status = 'inactive'
    WHERE user_id = 1
""")

conn.commit()

# View audit log
cursor.execute("""
    SELECT log_id, user_id, action, new_values, changed_at
    FROM user_audit_log
    ORDER BY changed_at DESC
""")

for log_id, user_id, action, new_values, changed_at in cursor.fetchall():
    print(f"Log {log_id}: User {user_id} - {action} at {changed_at}")

conn.close()

🔑 Key Takeaways

• **Cascading operations** automatically propagate changes through related tables

• **Soft deletes** preserve data history and enable recovery

• **Views** can simplify data filtering across multiple queries

• **Transactions** ensure all-or-nothing operations for data consistency

• **Conflict resolution** strategies (REPLACE, INSERT OR IGNORE) handle constraint violations

• **Audit trails** provide compliance and debugging capabilities

• **Test complex updates** in a transaction first before committing

• **Triggers** can automate logging and validation

📚 Further Learning

Deepen your database knowledge:

• [Joins & Relations](/courses/python/database_basics/advanced/joins_and_relations) - Complex data relationships

• [Performance Optimization](/courses/python/database_basics/advanced/database_performance) - Optimize bulk operations

• [Challenges & Quizzes](/#) - Practice advanced modification patterns