SQL DROP COLUMN: Verify the Schema You Intend to Keep

The practice task

Remove an obsolete note field from a toy directory while retaining its ID and name columns. Inspect the resulting column list instead of assuming success from a query that never referenced the removed field.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE people(id INTEGER, name TEXT, obsolete_note TEXT);
INSERT INTO people VALUES (1,'Ari','old import marker');
ALTER TABLE people DROP COLUMN obsolete_note;

SELECT name FROM pragma_table_info('people') ORDER BY cid;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The schema inspection returns id and name. The obsolete_note field is absent. A normal SELECT id, name would show that those fields are readable, but would not by itself prove the obsolete field was removed.

The sample column has no index, constraint, trigger or view depending on it. A real column may participate in any of those structures, and the database can reject a drop that would leave an invalid schema. Review dependencies rather than treating every column as an isolated piece of text.

A destructive schema change also affects application readers and writers. Decide whether a staged migration is required, preserve any needed information and verify the actual target database before applying such a change outside a toy exercise. An isolated SQLite example does not establish readiness for another schema.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Inspect dependent constraints and schema objects.
• Verify the resulting schema, not only a successful subset query.
• Keep application result-column assumptions in the review.