ALTER & DROP
Overview
Schemas are rarely perfect on day one. As applications grow, you will need to add new columns, rename old ones, or change data types. ALTER TABLE allows you to mutate the physical structure of a live table without losing the millions of rows of data already inside it. DROP TABLE is the nuclear option—it completely annihilates the table structure and deletes all data within it instantly.
Syntax
-- --- ALTER TABLE (Modifying Live Schemas) ---
-- 1. Add a new column to an existing table
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
-- 2. Delete an existing column (Data is lost forever!)
ALTER TABLE users DROP COLUMN age;
-- 3. Modify an existing column's data type (Risky!)
-- (PostgreSQL Syntax)
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);
-- 4. Add a constraint after the fact
ALTER TABLE users ADD CONSTRAINT unique_phone UNIQUE (phone_number);
-- --- DROP TABLE (The Nuclear Option) ---
-- Completely deletes the table structure and ALL data instantly.
DROP TABLE temp_logs;Common Pitfalls
- Running
ALTER TABLEto change a column type on a massive production table (e.g., changingINTtoBIGINTon a table with 500 million rows). This forces the database to rewrite the entire physical table on disk, completely locking the table for hours and bringing down your production website. - Trying to
DROP TABLE customerswhen anorderstable has a Foreign Key pointing to it. The database will throw an error to protect referential integrity. You must drop the child table (orders) first, or useDROP TABLE customers CASCADE(which violently deletes all dependent links).
Interview Questions
DROP TABLE, TRUNCATE TABLE, and DELETE FROM?DROP destroys the table structure completely. TRUNCATE keeps the structure, but instantly wipes all rows by deallocating the disk space (very fast, but cannot be rolled back easily). DELETE FROM removes rows one-by-one, logging every deletion (slower, but safe and reversible).
Real-World Example
Adding a required column to a live table that already has millions of rows. (If you just add NOT NULL, it crashes because existing rows are instantly invalid!)
-- Step 1: Add the column allowing NULLs initially
ALTER TABLE products ADD COLUMN is_digital BOOLEAN;
-- Step 2: Backfill the data for all existing millions of rows
UPDATE products SET is_digital = FALSE;
-- Step 3: NOW enforce the constraint safely!
ALTER TABLE products ALTER COLUMN is_digital SET NOT NULL;Check Your Knowledge
Test your understanding of ALTER & DROP with these quick questions.