DROP TABLE and TRUNCATE

DELETE is a DML command that removes specific rows based on a WHERE condition. It is fully transactional in all databases, fires row-level triggers, respects FK cascade rules, and logs each row deletion individually. It can be rolled back even after execution if inside an uncommitted transaction. On large tables without a WHERE clause, DELETE is extremely slow because every row deletion is individually logged.

TRUNCATE is a DDL command that removes all rows from a table at once by deallocating storage pages rather than removing rows one by one. It is dramatically faster than DELETE for full-table clearance — often 100-1000x faster. TRUNCATE does not fire row-level triggers, does not log individual row deletions, and resets auto-increment sequences when RESTART IDENTITY is specified. In PostgreSQL, TRUNCATE is transactional and can be rolled back. In MySQL, TRUNCATE auto-commits and cannot be rolled back — it is not transactional. TRUNCATE has no WHERE clause; it is all-or-nothing.

DROP TABLE removes the entire table — not just the data, but the table structure itself including all column definitions, indexes, constraints, sequences, and triggers. After DROP TABLE, the table does not exist at all. Any query, view, FK, or application code that references the dropped table will fail immediately. DROP TABLE is transactional in PostgreSQL (can be rolled back in a transaction) and effectively permanent in MySQL. Use DELETE for selective row removal in production, TRUNCATE for rapidly emptying dev/staging tables, and DROP TABLE for decommissioning tables during schema changes or development.

TRUNCATE is faster because it works at the storage level rather than the row level. DELETE removes rows one by one — for each row, it writes a log record for rollback, checks all FK constraints and triggers, updates all indexes, and marks the row as deleted. On a table with 10 million rows and 5 indexes, DELETE makes 10 million log writes, 10 million × 5 = 50 million index updates, and 10 million trigger evaluations. This is O(n) work proportional to the number of rows.

TRUNCATE bypasses all of this by deallocating the data pages directly. The table's storage pages are marked as free without examining individual rows. No row-by-row logging, no index updates per row, no trigger evaluations. It is essentially an O(1) operation regardless of how many rows exist. The speed difference becomes dramatic at scale — clearing 10 million rows with DELETE might take 10 minutes; TRUNCATE takes under a second.

Choose DELETE despite the speed difference when: you need to remove only specific rows (TRUNCATE has no WHERE clause), when triggers must fire (DELETE fires them, TRUNCATE does not — auditing triggers, denormalisation triggers, notification triggers all require DELETE), when FK cascade rules must be processed row by row, when you need reliable rollback in MySQL (TRUNCATE MySQL auto-commits), or when you need to verify exactly which rows were removed using RETURNING. TRUNCATE is the right choice only when you need to empty an entire table and none of these conditions apply — primarily for dev/staging resets and clearing temporary/staging tables.

CASCADE with DROP TABLE automatically removes all database objects that depend on the dropped table. The cascade scope includes: foreign key constraints in other tables that reference the dropped table (the FK constraint is removed, but the referencing table and its rows remain), views that query the dropped table, triggers that use the dropped table, and any other objects that depend on the table's existence.

A critical subtlety: CASCADE removes the FK constraints from referencing tables but does NOT remove the referencing tables or their rows. After DROP TABLE customers CASCADE, the orders table still exists with all its rows — but the FK constraint fk_orders_customer_id is gone. Orders rows now have customer_id values that reference a non-existent customers table. Referential integrity is broken silently. The database no longer prevents inserting orders for non-existent customers.

This makes CASCADE extremely dangerous on parent tables in a live schema. Before using it, always check what will cascade: in PostgreSQL, query pg_depend to find all objects that depend on the table. In MySQL, examine INFORMATION_SCHEMA.KEY_COLUMN_USAGE to find FK relationships. The professional rule: use CASCADE for cleanup scripts in development where breaking referential integrity temporarily is acceptable, and for dropping the entire chain of tables in the correct order. Never use DROP TABLE ... CASCADE in production without a complete understanding of every object it will affect and a verified backup.

Temporary tables are tables that exist only for the duration of a database session (or transaction, if created with ON COMMIT DROP). They are automatically dropped when the session ends — no manual cleanup required. Temporary tables are private to the session that created them — other sessions cannot see or access them, even if they create temporary tables with the same name.

Temporary tables are appropriate for: storing intermediate results of a complex multi-step query that is referenced multiple times (avoiding repeated subquery execution), breaking a complex single-query problem into readable sequential steps, creating a working dataset for an ETL step that is then processed and discarded, and providing a staging area for data that will be validated and then inserted into a permanent table.

The key advantage over CTEs (Common Table Expressions) for the same purpose: temporary tables can have indexes added to them, which CTEs cannot. If an intermediate result set is large and queried multiple times with filters, a temporary table with an index on the filter column is significantly faster than a CTE. The key disadvantage: temporary tables are session-specific and add operational complexity — if a session crashes, the table may not be cleaned up immediately. For most use cases in analytics and reporting, a CTE is simpler and sufficient. Reach for a temporary table when the intermediate result is large (millions of rows), reused many times, or needs to be indexed for performance.

The most important safeguard is a database name check at the start of any destructive script. A PL/pgSQL block that raises an exception if the current database is not the expected staging database name prevents accidental execution on production: IF current_database() != 'freshmart_staging' THEN RAISE EXCEPTION 'ABORT: wrong database %', current_database(); END IF. This single check has prevented more production incidents than any other safeguard.

For the reset itself, TRUNCATE with RESTART IDENTITY is the right tool — it empties all tables instantly and resets auto-increment sequences so new seed data starts from ID 1. TRUNCATE in the correct dependency order: child tables first (order_items, then orders), then parent tables (customers, products, stores). In PostgreSQL, TRUNCATE CASCADE on the top-level parent tables can handle the ordering automatically but requires understanding exactly which tables will be cascaded.

Additional safeguards: use separate database users for production and staging with different credentials, never copy production credentials to staging environment config files, configure your deployment pipeline to use environment-specific connection strings with no overlap, and add a second safeguard inside the application — a boolean flag like ALLOW_DESTRUCTIVE_OPERATIONS that is set to false in production and true only in dev/staging environments. Log every destructive operation with a timestamp, the executing user, and the database name for audit purposes. For extra safety, many teams require a JIRA ticket or Slack approval before running any staging reset that affects shared staging environments used by multiple teams.

Cause: You tried to TRUNCATE a parent table that has child tables with FK references to it. TRUNCATE cannot process FK cascade rules the same way DELETE does — it cannot selectively cascade or check each row. The database blocks the operation to prevent leaving orphaned rows in child tables that reference a now-empty parent.

Fix: TRUNCATE in dependency order — child tables first, then parents: TRUNCATE order_items; TRUNCATE orders; TRUNCATE customers; etc. Alternatively, use TRUNCATE TABLE parent CASCADE (PostgreSQL) which automatically truncates all FK-referencing child tables too — but verify you want to truncate all child tables before using CASCADE. In MySQL with FK checks enabled, temporarily disable them: SET FOREIGN_KEY_CHECKS = 0; TRUNCATE ...; SET FOREIGN_KEY_CHECKS = 1; — only in dev/staging, never in production with live traffic.

Cause: You tried to DROP TABLE on a table that does not exist. This happens when a setup script is run in an environment where the table was never created, when you are connected to the wrong database or schema, or when the table was already dropped in a previous run of the script.

Fix: Use DROP TABLE IF EXISTS customers — this is a no-op if the table does not exist, rather than an error. IF EXISTS is the standard idiom for cleanup scripts that may be run multiple times: DROP TABLE IF EXISTS order_items CASCADE; DROP TABLE IF EXISTS orders CASCADE; etc. It makes scripts idempotent — safe to run multiple times with the same result.

Cause: MySQL's TRUNCATE is NOT transactional. Even inside an explicit BEGIN/COMMIT block, TRUNCATE auto-commits immediately. If subsequent statements fail and you attempt ROLLBACK, the TRUNCATE cannot be reversed. This catches many developers who assume all DDL is transactional because it is in PostgreSQL.

Fix: In MySQL, if you need to empty a table with rollback capability, use DELETE instead: DELETE FROM table_name — slower but fully transactional. For test isolation in MySQL, use DELETE inside transactions rather than TRUNCATE. For staging resets where rollback is not needed, TRUNCATE is fine — just know it is permanent the moment it runs. If you have already lost data, restore from the most recent backup.

Cause: DROP TABLE ... CASCADE removed the customers table and also removed the FK constraint from orders that referenced customers. The orders table still exists with all its rows, but orders.customer_id is now an unconstrained integer column. Nothing prevents inserting orders with any customer_id value, including values that never existed as customers. Referential integrity is silently broken.

Fix: Restore customers table from backup and re-establish FK constraints. Going forward: never use DROP TABLE ... CASCADE on a parent table in a live schema without a clear plan for the referential integrity of all child tables. Use RESTRICT (the default) to force you to explicitly handle all dependent objects before dropping. Check dependent objects before dropping: SELECT tablename FROM pg_tables WHERE schemaname = 'public' and manually trace all FK relationships to understand the full impact of a cascade.

Cause: The temporary table was created with ON COMMIT DROP — it was automatically dropped at the end of the transaction. Or, if using connection pooling, the session that created the temp table was returned to the pool and a different session (without the temp table) handled the subsequent query. Temporary tables are session-scoped, not global.

Fix: For ON COMMIT DROP tables, create the temp table and use it within the same transaction. For connection pooling issues, create the temp table within the same database session/connection and ensure the same connection handles all subsequent queries that use it. If temp tables need to survive across multiple application requests, consider using a regular table with a session_id column instead, and clean it up with a scheduled job or at the start of each session. Alternatively, use CTEs (WITH clause) which are scoped to a single query and do not have session-scope issues.

Write a complete database reset script for FreshCart's staging environment. The script should: (1) Check that the current database is 'freshmart_staging' and abort with a clear error message if not. (2) Truncate all six FreshCart tables in the correct dependency order, restarting all sequences. (3) After truncating, insert one test store (ST001, 'FreshCart Test Store', 'Seattle', 'Karnataka', 'Test Manager', today's date, monthly target 500000). (4) Verify the reset worked by showing row counts for all six tables.