Chapter 11 · Defining Databases and Tables
DROP Statements, Dependencies, and Reversible Changes
DROP is irreversible only when the operator has no recovery path. Production-safe removal begins with dependency discovery and deprecation, continues with backup and verification, and ends with deliberate cleanup.
Learning outcomes
Removing a schema object can affect data, dependent objects, application code, permissions, backups, analytics, and audit obligations. A safe DROP workflow proves that the object is unused and recoverable before removal.
Distinguish DROP from DELETE and TRUNCATE.
Discover direct and external dependencies before removing an object.
Use IF EXISTS carefully without hiding unexpected drift.
Stage object deprecation and preserve a recovery path.
Execute and verify transactional removals in SQLite.
Data removal versus object removal
| Operation | Removes rows | Removes definition | Typical rollback concern |
|---|---|---|---|
| DELETE | Selected or all rows. | No. | Recover deleted data and referential side effects. |
| TRUNCATE | All rows in products that support it. | No. | Vendor-specific identity, logging, and transaction behavior. |
| DROP TABLE | All table data and the table definition. | Yes. | Recreate structure, dependent objects, permissions, and data. |
| DROP VIEW / INDEX / TRIGGER | No base-table rows. | Removes that object. | Restore definition and dependent behavior. |
The dependency graph
Database catalog dependencies are only part of the graph. External clients, exports, and operational procedures can depend on the same object without appearing in the schema catalog.
Inspect SQLite dependencies
SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE sql LIKE '%course%' OR tbl_name = 'course'ORDER BY type, name;PRAGMA foreign_key_list('enrollment');PRAGMA index_list('course');PRAGMA table_xinfo('course');Text search is a useful starting point, not a complete parser. Also search source repositories, migration history, scheduled jobs, BI tools, ORM models, exports, and monitoring queries.
DROP syntax and existence guards
DROP VIEW IF EXISTS active_course_catalog;DROP INDEX IF EXISTS uq_course_department_code;DROP TRIGGER IF EXISTS trg_course_audit;DROP TABLE IF EXISTS obsolete_course_import;IF EXISTS makes cleanup scripts repeatable, but it can also hide drift. In a controlled migration, an unexpectedly missing object may deserve a failed assertion rather than silent success.
Foreign keys change DROP behavior
PRAGMA foreign_keys = ON;SELECT COUNT(*) AS child_rowsFROM enrollment;-- Removing course while enrollment still references it is unsafe.-- The exact outcome depends on the declared referential actions.DROP TABLE course;SQLite performs foreign-key processing while dropping a table when enforcement is enabled. A parent table with dependent rows may fail to drop or may trigger declared cascades. Never treat DROP as independent of data relationships.
Prefer deprecation before destruction
Measure use
Log queries, inspect code, and monitor scheduled consumers before removal.
Stop new use
Remove creation privileges, mark the object deprecated, and update documentation.
Move consumers
Provide a replacement view, table, API, or export and verify adoption.
Time-box cleanup
Drop only after the observation window and recovery point are approved.
ALTER TABLE legacy_course_importRENAME TO deprecated_course_import_2026_08;-- After the agreed observation and retention window:DROP TABLE deprecated_course_import_2026_08;Create a reversible archive
ATTACH DATABASE 'schema_archive.db' AS archive;CREATE TABLE archive.course_2026_08 ASSELECT * FROM main.course;CREATE TABLE archive.enrollment_2026_08 ASSELECT * FROM main.enrollment;SELECT (SELECT COUNT(*) FROM main.course) AS source_courses, (SELECT COUNT(*) FROM archive.course_2026_08) AS archived_courses, (SELECT COUNT(*) FROM main.enrollment) AS source_enrollments, (SELECT COUNT(*) FROM archive.enrollment_2026_08) AS archived_enrollments;DETACH DATABASE archive;CREATE TABLE AS SELECT does not preserve keys, checks, indexes, triggers, views, ownership, or grants. Keep version-controlled DDL and a tested database backup when full restoration is required.
Transactional DROP rehearsal
BEGIN IMMEDIATE;DROP VIEW active_course_catalog;DROP TABLE enrollment;DROP TABLE course;SELECT name, typeFROM sqlite_schemaWHERE name IN ('active_course_catalog', 'enrollment', 'course');ROLLBACK;SELECT name, typeFROM sqlite_schemaWHERE name IN ('active_course_catalog', 'enrollment', 'course')ORDER BY type, name;SQLite DDL is transactional. A rehearsal inside a rollback can verify dependency order and expected catalog state, but it does not replace backup restoration testing.
Reusable Chapter 11 practice schema
Run this SQLite script in a disposable database before the hands-on exercises. It establishes a small academic domain with strict tables, generated data, composite uniqueness, foreign keys, a view, and representative rows.
PRAGMA foreign_keys = ON;DROP VIEW IF EXISTS active_course_catalog;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS department;CREATE TABLE department ( department_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL UNIQUE, budget_cents INTEGER NOT NULL DEFAULT 0 CHECK (budget_cents >= 0), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, hired_on TEXT NOT NULL CHECK (date(hired_on) IS NOT NULL), active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), instructor_id INTEGER REFERENCES instructor(instructor_id) ON DELETE SET NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, credits INTEGER NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 6), capacity INTEGER NOT NULL DEFAULT 30 CHECK (capacity > 0), published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), display_name TEXT GENERATED ALWAYS AS (course_code || ' · ' || title) VIRTUAL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (department_id, course_code)) STRICT;CREATE TABLE enrollment ( course_id INTEGER NOT NULL REFERENCES course(course_id) ON DELETE CASCADE, student_id INTEGER NOT NULL, enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, status TEXT NOT NULL DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'completed', 'withdrawn')), PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;CREATE VIEW active_course_catalog ASSELECT c.course_id, d.code AS department_code, c.course_code, c.title, c.credits, c.capacityFROM course AS cJOIN department AS d ON d.department_id = c.department_idWHERE c.published = 1;INSERT INTO department (department_id, code, name, budget_cents) VALUES (1, 'DATA', 'Data Engineering', 25000000), (2, 'CS', 'Computer Science', 30000000);INSERT INTO instructor (instructor_id, department_id, email, full_name, hired_on)VALUES (10, 1, 'nadia@example.edu', 'Nadia Rahimi', '2024-09-01'), (11, 2, 'omar@example.edu', 'Omar Haddad', '2023-02-15');INSERT INTO course (course_id, department_id, instructor_id, course_code, title, credits, capacity, published)VALUES (100, 1, 10, 'SQL-101', 'SQL Foundations', 3, 40, 1), (101, 1, 10, 'DE-201', 'Data Pipelines', 4, 30, 1), (102, 2, 11, 'DB-220', 'Database Systems',4, 35, 0);INSERT INTO enrollment (course_id, student_id, status) VALUES (100, 1001, 'enrolled'), (100, 1002, 'completed'), (101, 1001, 'enrolled');Safe dependency-order exercise
BEGIN IMMEDIATE;-- 1. Remove dependent view.DROP VIEW active_course_catalog;-- 2. Remove relationship table that references course.DROP TABLE enrollment;-- 3. Remove course after its direct dependents are gone.DROP TABLE course;-- Verify absence before deciding whether to commit.SELECT name, typeFROM sqlite_schemaWHERE name IN ('active_course_catalog', 'enrollment', 'course');ROLLBACK;Recovery runbook
| Requirement | Evidence |
|---|---|
| Definition recovery | Version-controlled CREATE statements for tables, indexes, views, triggers, and grants. |
| Data recovery | Verified backup, snapshot, export, or archive with row-count and checksum evidence. |
| Dependency recovery | List of applications, jobs, reports, and owners affected by rollback. |
| Recovery objective | Expected restoration time and acceptable data-loss window. |
| Decision authority | Named approver for drop, rollback, and retention exceptions. |
Checkpoint
Approve the removal
- How does DROP TABLE differ from DELETE FROM table?
- Why is sqlite_schema search insufficient by itself?
- When can IF EXISTS be harmful?
- Why is CTAS not a complete backup?
- What is the purpose of a rollback rehearsal?
Review the answers
DROP removes both data and definition; DELETE preserves the table. The catalog cannot reveal every external consumer. IF EXISTS can conceal unexpected drift. CTAS omits constraints and surrounding objects. A rollback rehearsal verifies dependency order and transactional behavior before an irreversible production decision.
Chapter 11 summary
- Databases and schemas define product-specific namespace boundaries.
- CREATE TABLE translates data meaning into a durable contract.
- Constraints protect identity, references, and row domains.
- Schema evolution should be staged, compatible, validated, and versioned.
- DROP requires dependency discovery, deprecation, recovery evidence, and verification.
Chapter 12 builds on these DDL skills by studying functional dependencies, normal forms, update anomalies, and practical normalization decisions.