SEO PACKAGE

1. SEO Title

ALTER TABLE vs DROP TABLE in SQL: Reshape Schemas Without Losing a Single Row

2. SEO Subtitle

A practical, beginner-friendly guide to modifying database structures safely — and understanding why one careless line of SQL can erase an entire table.

3. Featured Quote

"ALTER TABLE reshapes the room while people are still living in it. DROP TABLE demolishes the building. Know which one you're holding before you run it."

4. Meta Description

Learn ALTER TABLE and DROP TABLE in SQL, the difference between DELETE, TRUNCATE, and DROP, and how to change database schemas safely without data loss.

5. URL Slug

sql-alter-table-vs-drop-table-guide

6. Keywords

SQL ALTER TABLE, SQL DROP TABLE, DELETE vs TRUNCATE vs DROP, SQL schema changes, DDL vs DML, add column SQL, drop column SQL, rename column SQL, change column type SQL, SQL constraints, database migration basics, SQL for beginners, backend development SQL, production database safety, foreign key constraints, MySQL vs PostgreSQL syntax, SQL tutorial, relational database schema, SQL data definition language, safe schema modification

7. Tags

SQL · Database · ALTER TABLE · DROP TABLE · DDL · DML · Backend Development · MySQL · PostgreSQL · Database Design · SQL Tutorial · Coding Interview · Software Engineering · Data Structures

---

ALTER TABLE vs DROP TABLE: How to Change a Database Schema Without Breaking Production

8. Introduction

Every developer who works with data eventually hits the same wall: the table you designed six months ago no longer fits the product you're building today. A new feature needs an email column. A field you thought you'd need turns out to be dead weight. A VARCHAR(100) isn't long enough anymore.

The question is never whether your schema will change. It's how safely you can change it.

This lesson covers two of the most consequential commands in SQL — ALTER TABLE and DROP TABLE — plus the three commands people confuse constantly: DELETE, TRUNCATE, and DROP. These aren't obscure edge-case features. They show up in almost every backend codebase, every database migration, and a surprising number of coding interviews.

Interviewers ask about these commands because they reveal something deeper than syntax memorization. They test whether you understand the difference between structure and data, whether you know which operations are reversible, and whether you can be trusted near a production database. Getting DROP and TRUNCATE mixed up in an interview is a quiet red flag. Getting it mixed up in production is a resume-updating event.

By the end of this article, you'll modify schemas with confidence instead of fear — and you'll understand exactly why one line of SQL can erase a million rows with no undo button waiting to save you.

9. Problem Overview

Let's define the problem precisely, because the whole lesson hinges on one distinction.

A database table has two separate things living inside it:

• The schema — the blueprint. Which columns exist, what data type each column holds, and what rules (constraints) apply. This is the shape.
• The data — the actual rows you've stored. This is what you pour into the shape.

Most confusion in SQL comes from forgetting that these two things are independent. You can change the shape without touching the data, and you can change the data without touching the shape.

Commands that redesign the shape belong to DDL (Data Definition Language). Think of DDL as moving walls in a room. ALTER TABLE and DROP TABLE are DDL.

Commands that change the rows belong to DML (Data Manipulation Language). Think of DML as rearranging furniture inside the room. INSERT, UPDATE, and DELETE are DML.

The core problem this lesson solves:

You have a table full of live data. You need to change its structure — or remove it entirely — without accidentally destroying rows you can't get back.

10. Example

Imagine a users table with 100 rows and this starting shape:

id	name
1	Aisha Khan
2	Marcus Lee
…	…

Adding a column. Your product now needs email addresses. You add an email column:

ALTER TABLE users ADD COLUMN email VARCHAR(150);

Every one of the 100 rows instantly gets an email column, filled with NULL until you enter real values. Nothing you already stored disappears.

id	name	email
1	Aisha Khan	NULL
2	Marcus Lee	NULL

Dropping a column. Later you realize you never use email. You remove just that column:

ALTER TABLE users DROP COLUMN email;

The data inside that one column is gone forever, but every other column stays perfectly intact. You deleted one slice of the table, not the whole table.

Dropping the whole table. The users feature gets cut entirely:

DROP TABLE users;

The blueprint and all 100 rows vanish together. There is no undo.

Each output tells the same story from a different angle: ALTER edits the shape and preserves data; DROP TABLE erases everything.

11. Intuition

Here's how an experienced engineer reasons about this before writing a single command.

The mental model is a bookshelf.

• The shelf itself, with its labeled slots, is the schema.
• The books sitting in those slots are the data.

Now walk through the operations:

• Adding a column is nailing a new labeled slot onto every shelf at once. The existing books don't move. The new slot is just empty (NULL) until you fill it.
• Dropping a column is removing one slot. Whatever was in that slot is thrown away, but every other slot keeps its books.
• Renaming a column is a label swap. The name on the slot changes; every book inside stays exactly where it was.
• Retyping a column is a resize — stretching a slot from 100 characters wide to 150 so bigger books fit.
• Dropping the table isn't emptying a shelf. It's hauling the entire bookshelf to the dump.

Once you internalize this, the safety rules become obvious. Widening a slot is safe. Shrinking a slot is dangerous — if a book no longer fits, it either gets rejected or chopped. Removing a slot is permanent. Removing the whole shelf is catastrophic.

The experienced engineer's instinct is therefore: prefer editing the shelf in place (ALTER) over demolishing and rebuilding it (DROP + CREATE), because rebuilding means physically moving every book out and back in — slow, and every move is a chance to drop one.

12. Brute Force Solution — Drop and Recreate

The idea. You need to change a column's type or restructure the table. The naive approach: back up the data, DROP TABLE, CREATE TABLE with the perfect new shape, then re-insert everything.

The algorithm.

1. SELECT all rows into a temporary holding place (or export them).
2. DROP TABLE users;
3. CREATE TABLE users (...) with the corrected schema.
4. INSERT all the saved rows back in.

Advantages.

• Total control. You get a pristine, exactly-shaped table.
• Sometimes genuinely necessary when the transformation is too complex for a single ALTER.

Disadvantages.

• You must move all the data out and back in — slow and risky.
• Between the DROP and the finished re-insert, the table doesn't exist. Anything querying it breaks.
• If a foreign key points at this table, DROP can break that relationship.
• One failed step during re-insert and you're staring at a half-migrated table.

Time complexity: O(n) — every one of the n rows is read out and written back. Space complexity: O(n) — you temporarily hold a full copy of the data.

This works, but it treats a scalpel job like demolition.

13. Optimal Solution — ALTER TABLE In Place

ALTER TABLE edits the structure in place, so your rows never leave the table. Here's every operation it gives you.

Add a column

ALTER TABLE users ADD COLUMN email VARCHAR(150);

Drop a column

ALTER TABLE users DROP COLUMN email;

Rename a column (syntax differs by database — check yours first)

-- PostgreSQL
ALTER TABLE users RENAME COLUMN username TO handle;

-- MySQL (older versions)
ALTER TABLE users CHANGE username handle VARCHAR(100);

Change a column's type

-- PostgreSQL
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);

-- MySQL
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);

Add a constraint later — you don't have to define every rule when you first build the table. You can bolt rules on as the business figures out what it needs:

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Here's a quick reference for how each operation treats your data:

Operation	Touches the schema?	Touches the data?	Reversible?
ADD COLUMN	Yes	No (new column is NULL)	Yes (drop the column)
DROP COLUMN	Yes	Yes (that column's data gone)	No
RENAME COLUMN	Yes	No	Yes (rename back)
Change column type	Yes	Maybe (may truncate/fail)	Sometimes
ADD CONSTRAINT	Yes	No (but may reject bad rows)	Yes (drop constraint)
DROP TABLE	Yes (destroys it)	Yes (all rows gone)	No

And the three commands people confuse most:

Command	What it removes	Table remains?	Analogy
DELETE	Specific rows you choose	Yes	A spoon
TRUNCATE	Every row	Yes (empty)	A bucket
DROP	The entire table	No	A demolition crew

14. Python Code

Here's a clean, runnable example using Python's built-in sqlite3 — no external dependencies — that demonstrates the safe ALTER workflow and the difference between DELETE, TRUNCATE-style clearing, and DROP.

import sqlite3


def demo_schema_changes():
    """Demonstrate safe schema modification vs destructive operations."""
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()

    # Start with a minimal schema: just id and name.
    cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    cur.executemany(
        "INSERT INTO users (name) VALUES (?)",
        [("Aisha Khan",), ("Marcus Lee",), ("Priya Rao",)],
    )

    # ADD a column — existing rows survive, new column is NULL.
    cur.execute("ALTER TABLE users ADD COLUMN email TEXT")
    rows = cur.execute("SELECT id, name, email FROM users").fetchall()
    assert rows == [
        (1, "Aisha Khan", None),
        (2, "Marcus Lee", None),
        (3, "Priya Rao", None),
    ], "Data must survive an ADD COLUMN"

    # DELETE removes specific rows; the table stays.
    cur.execute("DELETE FROM users WHERE name = ?", ("Marcus Lee",))
    remaining = cur.execute("SELECT COUNT(*) FROM users").fetchone()[0]
    assert remaining == 2, "DELETE should remove one row, not the table"

    # 'TRUNCATE'-style clear: empty every row but keep the table standing.
    cur.execute("DELETE FROM users")  # sqlite has no TRUNCATE keyword
    assert cur.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 0

    # The table still exists after clearing — we can insert again.
    cur.execute("INSERT INTO users (name) VALUES (?)", ("New User",))
    assert cur.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 1

    # DROP removes the table entirely — blueprint and data both gone.
    cur.execute("DROP TABLE users")
    try:
        cur.execute("SELECT * FROM users")
        raise AssertionError("Table should not exist after DROP")
    except sqlite3.OperationalError:
        pass  # Expected: no such table

    conn.close()
    print("All schema-change assertions passed.")


if __name__ == "__main__":
    demo_schema_changes()

15. Code Walkthrough

• sqlite3.connect(":memory:") — spins up a throwaway in-memory database so you can experiment without creating files or risking real data. Perfect for learning.
• CREATE TABLE users (...) — establishes the initial schema: two columns, id and name.
• ALTER TABLE users ADD COLUMN email TEXT — the key line. It changes the shape while every existing row survives. The assert immediately after proves it: all three names are still there, each with a None (SQL NULL) email.
• DELETE FROM users WHERE name = ? — surgical removal of a single row. The count drops from 3 to 2, and the table is untouched structurally.
• DELETE FROM users (no WHERE) — empties every row. This is the closest SQLite gets to TRUNCATE; the table itself still stands, proven by the successful INSERT right after.
• DROP TABLE users — the demolition. The final try/except confirms the table no longer exists: querying it raises OperationalError: no such table.

Every destructive step is guarded by an assertion, so if any assumption were wrong, the program would fail loudly instead of lying to you.

16. Dry Run

Let's trace the users table through the whole program.

1. After CREATE + inserts → [(1, Aisha), (2, Marcus), (3, Priya)]. Schema: id, name.
2. After ADD COLUMN email → schema becomes id, name, email. Rows become [(1, Aisha, NULL), (2, Marcus, NULL), (3, Priya, NULL)]. No row lost.
3. After DELETE ... WHERE name='Marcus Lee' → [(1, Aisha, NULL), (3, Priya, NULL)]. Count = 2. Table intact.
4. After DELETE FROM users → []. Count = 0. Table still exists.
5. After INSERT 'New User' → [(4, New User, NULL)]. Proves the empty table was still usable.
6. After DROP TABLE users → the table is gone. The next SELECT throws no such table.

The trace shows the whole spectrum: preserve, remove one, remove all, destroy.

17. Complexity Analysis

• ALTER TABLE ... ADD COLUMN — usually O(1) metadata change in modern databases (the column is added logically; existing rows report NULL). Some engines rewrite the table, making it O(n).
• ALTER TABLE ... DROP COLUMN — depends on the engine; often O(1) metadata, sometimes O(n) if the table is rewritten.
• DROP TABLE — effectively O(1) to the user: the engine deallocates the table rather than deleting rows one by one. This is exactly why it's so fast and so dangerous.
• DELETE FROM ... WHERE — O(n) in the worst case (a full scan) unless an index narrows the search.
• TRUNCATE — near O(1), because it discards the data pages wholesale instead of logging each row like DELETE does.
• Drop-and-recreate (brute force) — O(n) time and O(n) extra space to hold the data copy.

The space cost matters most in the brute-force rebuild: holding a full duplicate of a large table can be the difference between a smooth migration and running out of disk.

18. Alternative Solutions

When a single ALTER isn't enough — say you're splitting one column into two, or transforming data as you migrate — the professional pattern is the expand-and-contract migration (also called blue-green schema change):

1. Expand: ADD the new column alongside the old one.
2. Backfill: copy/transform data from old to new with an UPDATE.
3. Switch: point application code at the new column.
4. Contract: once nothing reads the old column, DROP it.

Compared to drop-and-recreate, this keeps the table live the entire time, never leaves it missing, and each step is independently reversible. It's slower in wall-clock terms but dramatically safer — which is why it's the standard for zero-downtime deployments.

19. Edge Cases

• Empty table: ALTER still works and is fast — there are no rows to rewrite.
• Shrinking a type with data that doesn't fit: shrinking VARCHAR(150) to VARCHAR(5) either errors out or silently truncates, depending on the database and its mode. Test on a copy first.
• Duplicate values when adding a UNIQUE constraint: if existing rows already violate the rule, the ADD CONSTRAINT fails. Clean the data first.
• Foreign keys pointing at the table: DROP TABLE can break referential integrity or be blocked entirely by the constraint. Drop or update the dependents first.
• Minimum / maximum values: widening a numeric type (e.g. INT → BIGINT) is safe; narrowing it can overflow existing large values.
• NOT NULL on a column full of NULLs: adding a NOT NULL constraint to a populated column fails unless you provide a default or backfill first.

20. Common Mistakes

1. Confusing DROP, TRUNCATE, and DELETE. The classic: running TRUNCATE when you meant to delete one row. DELETE is a spoon, TRUNCATE is a bucket, DROP is a demolition crew.
2. Assuming there's an undo. The database doesn't ask "are you sure?" — it just does it. There is no recycle bin. Back up before anything destructive, every single time.
3. Shrinking a column type blind. The command looks harmless, but your stored values decide whether it survives. Test type changes on a copy.
4. Ignoring database dialect differences. The exact keyword for renaming or retyping differs between MySQL (MODIFY/CHANGE) and PostgreSQL (ALTER COLUMN ... TYPE). Copy-pasting cross-dialect SQL fails silently or loudly.
5. Dropping a table other tables depend on. A foreign key from another table can break, or the DROP can fail mid-way, leaving inconsistent state.
6. Adding a NOT NULL column with no default to a populated table — the operation is rejected because existing rows would violate the rule.

21. Interview Questions

• What's the difference between DDL and DML? Which category do ALTER and DROP fall into, and why?
• Explain the difference between DELETE, TRUNCATE, and DROP in terms of what remains afterward and rollback behavior.
• Is TRUNCATE faster than DELETE? Why?
• How would you add a NOT NULL column to a table that already has millions of rows without downtime?
• What happens if you DROP a table that another table references via a foreign key?
• Can you roll back a DROP TABLE? What about inside a transaction?
• How does changing a column's type risk data loss, and how would you do it safely?
• Why might you choose ALTER TABLE over dropping and recreating a table?

22. Similar Problems

While ALTER/DROP are schema commands rather than LeetCode algorithm puzzles, several LeetCode Database problems build the surrounding fluency:

• LeetCode 175 — Combine Two Tables: reinforces how table structure and joins depend on a well-defined schema.
• LeetCode 183 — Customers Who Never Order: teaches reasoning about NULL and missing relationships, exactly what you create when you ADD a nullable column.
• LeetCode 196 — Delete Duplicate Emails: direct practice with DELETE semantics — removing specific rows, the "spoon" operation from this lesson.
• LeetCode 627 — Swap Salary: practice with UPDATE, the DML sibling that changes data in place, the mirror image of ALTER changing structure in place.

They're related because mastering schema commands is the foundation that makes every query, join, and update above them predictable.

23. Key Takeaways

• A schema is the shape; data is what you pour into it. They are independent.
• ALTER TABLE reshapes a table that's already full — add, drop, rename, or retype columns and constraints — without losing rows. Prefer it over rebuilding.
• DROP TABLE removes the entire thing, blueprint and data both, with no undo.
• DELETE = spoon, TRUNCATE = bucket, DROP = demolition crew.
• The two mistakes that wreck live databases: dropping active tables and type changes that silently truncate. Back up first, test on a copy, and check your database's dialect.

24. Watch the Video

Reading builds the model; watching cements it. See every command run live — the ADD, the DROP COLUMN, the moment a shrunk type chops real data, and the difference between clearing and demolishing a table — in the full walkthrough here: {LINK}. Watch it once before your next migration and you'll never confuse TRUNCATE and DROP again.

25. About the Series

This lesson is part of Fun with Learning Technology, a series that breaks down programming and database concepts from the ground up — no hand-waving, no jargon dumps. Each episode takes one idea that developers think they understand, and rebuilds it with clear analogies, runnable examples, and the real-world gotchas that only show up in production. Whether you're preparing for coding interviews or shipping backend features, the series is designed to turn fuzzy familiarity into genuine confidence.

26. Call To Action

If this made schema changes click for you, here's how to help the series grow:

• 👍 Like the video on YouTube — it genuinely helps more developers find it.
• 🔔 Subscribe to the channel so you don't miss the next SQL lesson.
• 📩 Subscribe on Substack for the written deep-dives like this one.
• 💬 Comment with the worst database mistake you've made (we've all TRUNCATEd something) — your story helps someone else avoid it.
• 🔁 Share this with a teammate who's one careless DROP away from a very bad afternoon.

---

SOCIAL MEDIA

LinkedIn Post

One line of SQL can erase an entire table in under a second. It's called DROP, and there is no undo button waiting to save you.

I've watched capable engineers freeze up before touching a production schema — not because the syntax is hard, but because nobody explained the mental model. So here's the one that fixed it for me.

A table is a bookshelf. The labeled slots are the schema (the shape). The books are the data. Once you separate those two ideas, everything gets simpler:

• ALTER TABLE edits the shelf in place — add a slot, remove a slot, rename a slot, resize a slot — while every book stays put. Your rows are safe.

• DROP TABLE hauls the entire bookshelf to the dump. Blueprint and books, gone together, no recovery.

And the three commands people mix up constantly:

• DELETE removes specific rows you choose — a spoon. • TRUNCATE empties every row but keeps the empty table standing — a bucket. • DROP removes the whole table — a demolition crew.

The two mistakes that wreck live databases:

1. Dropping a table something else still depends on.
2. Shrinking a column's type so existing data silently gets chopped.

The fix is boring and non-negotiable: back up before anything destructive, and test type changes on a copy first. The database will never ask "are you sure?" — it just does it.

Master this and you'll modify schemas with confidence instead of fear.

Full written walkthrough with runnable Python examples is linked in the comments.

#SQL #Database #BackendDevelopment #SoftwareEngineering #CodingInterview

Twitter/X Thread

1/ One line of SQL can wipe a million rows in a second.

It's called DROP. There's no undo button.

Its safer cousin, ALTER TABLE, does the opposite — it reshapes a full table without losing a single row.

Here's when to reach for each one 🧵

2/ First, one word: schema.

A schema is the blueprint of your table — what columns exist, what type each holds, and the rules on them.

The rows are separate from that blueprint.

Schema = the shape. Data = what you pour into it.

3/ ALTER TABLE and DROP are both DDL — Data Definition Language. They redesign the room (move walls).

That's different from DML (INSERT/UPDATE/DELETE), which just rearranges the furniture inside.

4/ Adding a column is painless:

ALTER TABLE users ADD COLUMN email VARCHAR(150);

100 rows, no email column? Run this and all 100 instantly get one, filled with NULL. Nothing you stored disappears.

5/ Dropping a column is just as easy:

ALTER TABLE users DROP COLUMN email;

The data in THAT column is gone forever — but every other column stays perfectly intact. You deleted one slice, not the table.

6/ You can also rename a column (a label swap) or retype it (a resize — stretch VARCHAR(100) to 150).

One heads-up: the exact keyword differs between MySQL and Postgres. Check your database before you run it.

7/ Now DROP TABLE — the big one.

This isn't emptying a shelf. It's taking the whole bookshelf to the dump. Blueprint AND every row vanish together. No undo. Use it only when a table is truly obsolete.

8/ The three everyone confuses:

DELETE = removes specific rows → a spoon TRUNCATE = empties every row, keeps the table → a bucket DROP = removes the table entirely → a demolition crew

I once truncated a table meaning to delete one row. Don't be me.

9/ Two mistakes that wreck live databases:

• Dropping a table another table still points to via a foreign key • Shrinking a column type so existing data silently gets chopped

The command looks fine. Your stored values decide whether it survives.

10/ The rule that saves you: back up before anything destructive, every single time. Test type changes on a copy first.

Full write-up + runnable Python examples 👇 {LINK}

Follow for more SQL from the ground up.

Facebook Post

Quick SQL truth that took me way too long to learn: one line — DROP — can erase an entire table in a second, and there's no undo.

Here's the mental model that makes it click. Think of a table as a bookshelf. The labeled slots are the schema (the shape). The books are your data.

ALTER TABLE edits the shelf in place — add a slot, remove one, rename or resize it — while every book stays put. DROP TABLE hauls the whole bookshelf to the dump.

And the three people always mix up: DELETE removes specific rows (a spoon), TRUNCATE empties the whole table but keeps it standing (a bucket), DROP removes the table completely (a demolition crew).

The golden rule: back up before anything destructive. The database never asks "are you sure?" — it just does it.

Full walkthrough with examples here: {LINK} 🙂

Reddit Summary

A clean mental model for ALTER TABLE vs DROP TABLE (and DELETE/TRUNCATE/DROP)

Sharing the framing that finally made schema changes stop being scary for me. Might help anyone newer to SQL.

Core idea: a table is two separate things — the schema (the shape: columns, types, constraints) and the data (the rows). Commands that change the shape are DDL; commands that change the rows are DML.

• ALTER TABLE edits the structure in place: add/drop/rename/retype columns and add constraints later, all without losing existing rows. Adding a column just fills existing rows with NULL. Dropping a column removes only that column's data.
• DROP TABLE removes the entire table — schema and data — with no undo.

The three that get confused:

• DELETE → removes specific rows, table stays.
• TRUNCATE → empties all rows, empty table stays. Faster than DELETE because it discards pages wholesale instead of logging each row.
• DROP → removes the table entirely.

Two real gotchas worth knowing: (1) dropping a table that a foreign key points to can break referential integrity, and (2) shrinking a column's type can silently truncate data that no longer fits — test on a copy. And prefer ALTER over drop-and-recreate, since rebuilding means moving all your data out and back in.

Happy to answer questions on any of these in the comments.

---

GITHUB README

# SQL: ALTER TABLE vs DROP TABLE — Changing Schemas Safely

> Reshape and remove database structures without losing data — and understand
> the critical difference between DROP, TRUNCATE, and DELETE.

## Problem

Your schema will always change: a feature needs a new column, an old field
becomes dead weight, a type outgrows its size. The real question is how to make
those changes **without accidentally destroying rows you can't recover**.

This covers two of the highest-stakes commands in SQL:

- `ALTER TABLE` — reshape a table that's already full of data, in place.
- `DROP TABLE` — remove a table entirely, blueprint and rows both, with no undo.

Plus the three commands developers confuse constantly: `DELETE`, `TRUNCATE`,
and `DROP`.

## Intuition

Think of a table as a **bookshelf**:

- The labeled slots are the **schema** (the shape).
- The books are the **data**.

| Operation           | Bookshelf analogy                     | Data impact              |
|---------------------|----------------------------------------|--------------------------|
| `ADD COLUMN`        | Nail on a new empty slot               | Existing books untouched |
| `DROP COLUMN`       | Remove one slot                        | That slot's books gone   |
| `RENAME COLUMN`     | Swap the label                         | Books stay put           |
| Change column type  | Resize the slot                        | May chop books if shrunk |
| `DROP TABLE`        | Haul the whole shelf to the dump       | Everything gone, no undo |

## Approach

Prefer `ALTER TABLE` over drop-and-recreate. `ALTER` edits in place so rows
never leave the table; rebuilding means moving every row out and back in — slow
and risky.

```sql
-- Add a column (existing rows get NULL)
ALTER TABLE users ADD COLUMN email VARCHAR(150);

-- Drop a column (only that column's data is lost)
ALTER TABLE users DROP COLUMN email;

-- Rename (syntax differs by dialect!)
ALTER TABLE users RENAME COLUMN username TO handle;   -- PostgreSQL

-- Change type
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150); -- PostgreSQL
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);     -- MySQL

-- Add a constraint later
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- Remove the whole table (NO UNDO)
DROP TABLE users;

DELETE vs TRUNCATE vs DROP

Command	Removes	Table remains?	Analogy
DELETE	Specific rows	Yes	Spoon
TRUNCATE	Every row	Yes (empty)	Bucket
DROP	The entire table	No	Demolition crew

Python Solution

Runnable with the standard library only (sqlite3):

import sqlite3


def demo_schema_changes():
    """Demonstrate safe schema modification vs destructive operations."""
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()

    cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    cur.executemany(
        "INSERT INTO users (name) VALUES (?)",
        [("Aisha Khan",), ("Marcus Lee",), ("Priya Rao",)],
    )

    # ADD a column — existing rows survive, new column is NULL.
    cur.execute("ALTER TABLE users ADD COLUMN email TEXT")
    assert cur.execute("SELECT email FROM users").fetchall() == [
        (None,), (None,), (None,)
    ]

    # DELETE removes specific rows; table stays.
    cur.execute("DELETE FROM users WHERE name = ?", ("Marcus Lee",))
    assert cur.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 2

    # Clear all rows but keep the table (TRUNCATE-style in sqlite).
    cur.execute("DELETE FROM users")
    cur.execute("INSERT INTO users (name) VALUES (?)", ("New User",))
    assert cur.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 1

    # DROP removes the table entirely.
    cur.execute("DROP TABLE users")
    try:
        cur.execute("SELECT * FROM users")
        raise AssertionError("Table should not exist after DROP")
    except sqlite3.OperationalError:
        pass  # Expected: no such table

    conn.close()
    print("All schema-change assertions passed.")


if __name__ == "__main__":
    demo_schema_changes()

Complexity

Operation	Time	Notes
ALTER ... ADD COLUMN	O(1) or O(n)	Metadata-only in many engines
DROP TABLE	~O(1)	Deallocates the table, not row-by-row
DELETE ... WHERE	O(n)	Full scan unless indexed
TRUNCATE	~O(1)	Discards data pages wholesale
Drop-and-recreate rebuild	O(n) time/space	Holds a full data copy

Common Mistakes

1. Confusing DROP, TRUNCATE, and DELETE.
2. Assuming there's an undo — there isn't. Back up first.
3. Shrinking a column type and silently truncating data.
4. Ignoring MySQL vs PostgreSQL dialect differences.
5. Dropping a table another table depends on via foreign key.

Video

📺 Watch the full walkthrough: {LINK}

Article

📖 Full written deep-dive with dry runs, edge cases, and interview questions: {LINK}

---

Part of the Fun with Learning Technology series — programming and database concepts explained from the ground up. ```

ALTER TABLE users
RENAME COLUMN email TO contact;

ALTER TABLE users
MODIFY contact VARCHAR(150);