SQL DDL — CREATE, ALTER, DROP, TRUNCATE

WHAT are the four verbs?

Verb	Acts on	Effect	Removes structure?	Removes data?	Rollback-able?
`CREATE`	new object	builds a table/index/view	—	—	usually no
`ALTER`	existing object	changes definition	partially	sometimes	usually no
`DROP`	existing object	deletes the whole object	yes	yes	no
`TRUNCATE`	table contents	empties all rows fast	no	yes	no

CREATE — building structure from scratch

HOW — the anatomy:

CREATE TABLE students (
    id          INT          PRIMARY KEY,         -- unique row identity
    name        VARCHAR(50)  NOT NULL,            -- text, must be present
    age         INT          CHECK (age >= 0),    -- domain rule
    email       VARCHAR(100) UNIQUE,              -- no duplicates
    dept_id     INT,
    joined_on   DATE         DEFAULT CURRENT_DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

ALTER — renovating without rebuilding

ALTER TABLE students ADD    phone VARCHAR(15);          -- new column
ALTER TABLE students DROP   COLUMN phone;               -- remove column
ALTER TABLE students ALTER  COLUMN age TYPE SMALLINT;   -- change type (PostgreSQL)
ALTER TABLE students RENAME COLUMN name TO full_name;   -- rename
ALTER TABLE students ADD CONSTRAINT chk_age CHECK (age < 130); -- add rule

Worked example Adding a NOT NULL column to a populated table

ALTER TABLE students ADD country VARCHAR(30) NOT NULL DEFAULT 'IN';

Why the DEFAULT? Existing rows have no value for the new column. If you say NOT NULL without a DEFAULT, those rows would violate the constraint instantly and the statement fails. The default fills them, so the rule can be satisfied.

DROP — demolishing the whole object

DROP TABLE students;          -- table + all rows + structure GONE
DROP TABLE IF EXISTS students;-- safe: no error if absent
DROP INDEX idx_name;
DROP DATABASE college;

TRUNCATE — emptying fast, keeping the shell

TRUNCATE TABLE students;   -- all rows vanish, table definition stays

Figure — SQL DDL — CREATE, ALTER, DROP, TRUNCATE

Forecast-then-Verify

Recall Predict before revealing

You run TRUNCATE TABLE students; then immediately ROLLBACK; (MySQL/InnoDB). Are the rows back?

Answer: No. TRUNCATE is DDL and auto-commits — the implicit commit happens before your ROLLBACK, so there is nothing to undo. Had you used DELETE, the rows would return.

Recall Predict

A departments table has rows referenced by students.dept_id. You run DROP TABLE departments;. What happens?

Answer: It fails (or requires CASCADE) because a foreign key still depends on it. The DB protects referential integrity. DROP TABLE departments CASCADE; would force it and also drop the dependent constraint.

Flashcards

What does DDL stand for and what does it manage?

Data Definition Language; it defines/modifies/removes schema objects (tables, indexes, views), i.e. structure, not row data.

Why are most DDL statements considered dangerous?

They auto-commit and usually cannot be rolled back, so DROP/TRUNCATE take immediate permanent effect.

PRIMARY KEY equals which two constraints combined?

UNIQUE + NOT NULL.

Difference between CHAR(n) and VARCHAR(n)?

CHAR pads to fixed n length; VARCHAR stores only actual length up to max n.

What does FOREIGN KEY enforce?

Referential integrity — value must exist in the referenced table's column.

When adding a NOT NULL column to a populated table, what must you supply and why?

A DEFAULT, so existing rows get a valid value and don't violate NOT NULL.

TRUNCATE vs DELETE: name 3 differences.

TRUNCATE has no WHERE, is faster (deallocates pages), resets identity, usually non-rollback; DELETE filters, is slower/logged, rollback-able, is DML.

Is TRUNCATE rollback-able in InnoDB?

No — it auto-commits as DDL.

Does ALTER ... DROP COLUMN delete the table?

No, it removes only that one column; the table survives.

Why use DROP TABLE IF EXISTS in scripts?

To avoid an error if the table is already absent, making the script idempotent.

Which DDL verb keeps structure but empties all data fastest?

TRUNCATE.

What error occurs dropping a table referenced by a foreign key?

Dependency error; needs CASCADE or drop the FK first.

Recall Feynman: explain to a 12-year-old

Imagine your data is a toy box.

CREATE = building a brand-new empty box with labelled sections ("cars here, blocks there").
ALTER = adding a new section to the box or relabelling one, without throwing away your toys.
DROP = throwing the entire box in the trash — toys, labels, everything.
TRUNCATE = dumping out all the toys but keeping the empty box with its labels, ready to refill. And once you've done CREATE/DROP/TRUNCATE, you can't say "undo" — it's already permanent.

Connections

SQL DML — INSERT, UPDATE, DELETE, SELECT — the data that lives inside DDL structures
Constraints — PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE — rules declared inside CREATE/ALTER
Normalization — why you split data into multiple CREATE-d tables
Transactions and ACID — why DDL auto-commit matters
Indexes — CREATE/DROP INDEX speeds up queries
Referential Integrity & CASCADE — why DROP can fail

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, database mein kuch bhi data daalne se pehle uska structure banana padta hai — yeh kaam karta hai DDL (Data Definition Language). Socho ek building bana rahe ho: pehle naksha aur kamre chahiye, tabhi furniture (data) rakhoge. DDL ke chaar main commands hain — CREATE se naya table banta hai (columns, types, constraints ke saath), ALTER se existing table mein change karte ho (naya column add, type change, rename) bina purana data udaaye, DROP se poora table hi delete ho jaata hai — structure bhi, data bhi, aur TRUNCATE se saare rows turant saaf ho jaate hain par table ka dhaancha bana rehta hai.

Sabse important baat: DDL auto-commit hota hai. Matlab DROP ya TRUNCATE chalaya toh turant pakka ho jaata hai — ROLLBACK se wapas nahi aayega. Isliye yeh commands khatarnak hain, hamesha backup lo aur scripts mein IF EXISTS lagao. Ek common galti: log sochte hain TRUNCATE bas tez DELETE hai — galat. TRUNCATE mein WHERE nahi laga sakte, foreign key reference ho toh chalta nahi, aur rollback nahi hota. Jab filter ya safety chahiye toh DELETE use karo (woh DML hai, transactional hai).

Yaad rakhne ka tareeka: CADT — Create, Alter, Drop, Truncate. Danger badhta jaata hai: Alter gentle hai, Drop poori building gira deta hai, Truncate sirf toys (data) bahar phenk deta hai par box (table) rakh leta hai. Exam aur real projects dono mein yeh difference clear hona zaroori hai, warna ek galat command se poora data ud sakta hai.

SQL DDL — CREATE, ALTER, DROP, TRUNCATE

WHAT are the four verbs?

CREATE — building structure from scratch

ALTER — renovating without rebuilding

DROP — demolishing the whole object

TRUNCATE — emptying fast, keeping the shell

Forecast-then-Verify

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections