Cascade Delete Nightmare — When Deleting One Row Deletes Ten Thousand

Introduction

ON DELETE CASCADE is a convenient feature: delete a parent row and all child rows automatically follow. It's also one of the most dangerous features in a relational database. A single mistyped WHERE clause, a test cleanup script, or an accidental API call can silently delete a subtree of data that took months to build. And since it happens inside the database engine, your application code never sees it coming.

The Anatomy of a Cascade Delete Disaster
Fix 1: Soft Deletes — Never Hard Delete
Fix 2: Remove ON DELETE CASCADE — Use RESTRICT Instead
Fix 3: Two-Phase Delete with Confirmation
Fix 4: Backup Before Delete
Fix 5: Audit Log for Deletions
Cascade Delete Checklist
Conclusion

The Anatomy of a Cascade Delete Disaster

-- Schema with cascades at every level
CREATE TABLE organizations (id UUID PRIMARY KEY, name TEXT);
CREATE TABLE users (
  id UUID PRIMARY KEY,
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE
);
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE
);
CREATE TABLE invoices (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE
);

-- Now:
DELETE FROM organizations WHERE name = 'test-org';

-- What actually happened:
-- 1 organization deleted
-- → N users deleted (cascade)
-- → N*M orders deleted (cascade from users)
-- → N*M*K order_items deleted (cascade from orders)
-- → N*M invoices deleted (cascade from orders)
-- All in one transaction, all instantaneous, all unrecoverable

Fix 1: Soft Deletes — Never Hard Delete

// Add deleted_at to every table instead of physically deleting
// This is the most important defense against accidental deletion

// Schema
// ALTER TABLE organizations ADD COLUMN deleted_at TIMESTAMPTZ;
// ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

// Soft delete
async function deleteOrganization(orgId: string, deletedBy: string) {
  await db.transaction(async (trx) => {
    // Soft delete org
    await trx.query(`
      UPDATE organizations
      SET deleted_at = NOW(), deleted_by = $2
      WHERE id = $1 AND deleted_at IS NULL
    `, [orgId, deletedBy])

    // Soft delete users
    await trx.query(`
      UPDATE users SET deleted_at = NOW()
      WHERE org_id = $1 AND deleted_at IS NULL
    `, [orgId])

    // Log the deletion
    await trx.query(`
      INSERT INTO deletion_log (resource_type, resource_id, deleted_by, deleted_at)
      VALUES ('organization', $1, $2, NOW())
    `, [orgId, deletedBy])
  })
}

// Queries filter out deleted records
const activeUsers = await db.query(
  'SELECT * FROM users WHERE org_id = $1 AND deleted_at IS NULL',
  [orgId]
)

// Recovery: just clear deleted_at
async function restoreOrganization(orgId: string) {
  await db.query('UPDATE organizations SET deleted_at = NULL WHERE id = $1', [orgId])
  await db.query('UPDATE users SET deleted_at = NULL WHERE org_id = $1', [orgId])
}

Fix 2: Remove ON DELETE CASCADE — Use RESTRICT Instead

-- ❌ Dangerous: silent cascade destruction
CREATE TABLE users (
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE
);

-- ✅ Safe: prevents deletion if children exist
CREATE TABLE users (
  org_id UUID REFERENCES organizations(id) ON DELETE RESTRICT
);

-- Now:
DELETE FROM organizations WHERE id = 'some-org';
-- → ERROR: update or delete on table "organizations" violates foreign key constraint
--   on table "users"
-- The error forces explicit handling — can't accidentally cascade

-- Safer to make deletion explicit in application code:
async function deleteOrganization(orgId: string) {
  const userCount = await db.queryOne('SELECT COUNT(*) FROM users WHERE org_id = $1', [orgId])

  if (userCount.count > 0) {
    throw new Error(`Cannot delete organization: ${userCount.count} users still exist`)
  }

  await db.query('DELETE FROM organizations WHERE id = $1', [orgId])
}

Fix 3: Two-Phase Delete with Confirmation

// Require preview + confirmation before destructive deletes

async function previewOrganizationDeletion(orgId: string) {
  // Show exactly what will be deleted
  const [orgResult, userResult, orderResult] = await Promise.all([
    db.query('SELECT name FROM organizations WHERE id = $1', [orgId]),
    db.query('SELECT COUNT(*) FROM users WHERE org_id = $1', [orgId]),
    db.query(`
      SELECT COUNT(*) FROM orders o
      JOIN users u ON o.user_id = u.id
      WHERE u.org_id = $1
    `, [orgId]),
  ])

  return {
    organization: orgResult.rows[0]?.name,
    userCount: parseInt(userResult.rows[0]?.count ?? '0'),
    orderCount: parseInt(orderResult.rows[0]?.count ?? '0'),
    warning: 'This action is irreversible',
  }
}

async function confirmOrganizationDeletion(orgId: string, confirmation: string) {
  const org = await db.queryOne('SELECT name FROM organizations WHERE id = $1', [orgId])

  // Require typing the org name to confirm
  if (confirmation !== org.name) {
    throw new Error('Confirmation text does not match organization name')
  }

  // Proceed with deletion
  await softDeleteOrganization(orgId)
}

Fix 4: Backup Before Delete

// For critical deletes — snapshot what you're about to delete
async function deleteWithBackup(orgId: string) {
  // Snapshot to backup table before deleting
  await db.query(`
    INSERT INTO organizations_deleted_backup
    SELECT *, NOW() as backed_up_at
    FROM organizations WHERE id = $1
  `, [orgId])

  await db.query(`
    INSERT INTO users_deleted_backup
    SELECT *, NOW() as backed_up_at
    FROM users WHERE org_id = $1
  `, [orgId])

  // Now safe to delete — you can restore from backup tables
  await db.query('DELETE FROM users WHERE org_id = $1', [orgId])
  await db.query('DELETE FROM organizations WHERE id = $1', [orgId])
}

Fix 5: Audit Log for Deletions

// Trigger: log every deletion automatically
// This at least gives you a record of what was deleted and when

// PostgreSQL trigger
await db.query(`
  CREATE OR REPLACE FUNCTION log_deletion()
  RETURNS TRIGGER AS $$
  BEGIN
    INSERT INTO deletion_log (table_name, row_id, deleted_data, deleted_at)
    VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), NOW());
    RETURN OLD;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER users_deletion_log
  BEFORE DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION log_deletion();
`)

Cascade Delete Checklist

✅ Use soft deletes (deleted_at) for any data you might need to recover
✅ Use ON DELETE RESTRICT instead of ON DELETE CASCADE for critical tables
✅ Require explicit application-layer deletion with business logic validation
✅ Show a preview of what will be deleted before confirming
✅ Require confirmation text for deletions affecting many rows
✅ Log all deletions to an audit/backup table with BEFORE DELETE trigger
✅ Test deletion paths in staging with production-like data volumes

Conclusion

ON DELETE CASCADE is a footgun. It's convenient for test cleanup, but in production it silently destroys data trees in milliseconds with no undo. The safest approach is to remove CASCADE from critical foreign keys and use application-layer deletion with explicit loops, validation, and audit logging. For most user data, soft deletes are better than hard deletes — you get the effect of deletion (user can't log in, data not shown) with the ability to recover if something goes wrong. Save ON DELETE CASCADE for truly disposable child records like session tokens, file chunks, or temporary state.