Dropping Tables

DROP TABLE permanently removes table structure and data.

Use it carefully, especially once your database contains meaningful records.

Core Concepts

flowchart TD
  A[Need table removed] --> B[Confirm table name]
  B --> C[Optional backup]
  C --> D[DROP TABLE]
  D --> E[Verify with .tables]

Command	Effect
DROP TABLE my_table;	Deletes table and all its rows
DROP TABLE IF EXISTS my_table;	Avoids error if table is missing

Code Examples

-- Create a throwaway table for demo.
CREATE TABLE temp_data (
  id    INTEGER PRIMARY KEY,
  value TEXT
);

Expected output
Table temp_data is created.

-- Drop it safely (no error if already absent).
DROP TABLE IF EXISTS temp_data;

Expected output
Table removal succeeds without result rows.

-- Confirm table is gone.
.tables

Expected output
temp_data no longer appears in table list.

SQLite-Specific Nuances

SQLite Nuance

SQLite has no recycle bin for dropped tables.

Recovery requires backups or external recovery tooling.

Common Pitfalls / Best Practices

Pitfall

Using DROP TABLE in the wrong database file because the active main path was not verified.

Best Practice

Run .databases and .tables immediately before destructive operations.

Quick Challenge

Write a defensive drop command for a table named sandbox.

View Solution

DROP TABLE IF EXISTS sandbox;

Expected output
Command succeeds whether sandbox exists or not.