SQL DDL — CREATE, ALTER, DROP, TRUNCATE — Hinglish notes

4.4.3 · Coding › Databases

WHAT are the four verbs?

Verb	Acts on	Effect	Removes structure?	Removes data?	Rollback-able?
`CREATE`	new object	table/index/view banata hai	—	—	usually no
`ALTER`	existing object	definition change karta hai	partially	sometimes	usually no
`DROP`	existing object	pura object delete kar deta hai	yes	yes	no
`TRUNCATE`	table contents	saari rows fast empty kar deta hai	no	yes	no

CREATE — structure ko scratch se banana

HOW — 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 — bina rebuild kiye renovation

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 Ek populated table mein NOT NULL column add karna

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

Why the DEFAULT? Existing rows mein nayi column ke liye koi value nahi hoti. Agar tum NOT NULL bolo bina DEFAULT ke, to wo rows turant constraint violate kar dengi aur statement fail ho jaayega. Default unhe fill kar deta hai, taaki rule satisfy ho sake.

DROP — pura object demolish karna

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 — fast empty karna, shell ko rakhna

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

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

Forecast-then-Verify

Recall Predict before revealing

Tum TRUNCATE TABLE students; run karte ho phir turant ROLLBACK; (MySQL/InnoDB). Kya rows wapas aati hain?

Answer: Nahi. TRUNCATE DDL hai aur auto-commits — implicit commit tumhare ROLLBACK se pehle hota hai, isliye undo karne ke liye kuch bacha hi nahi. Agar DELETE use kiya hota, to rows wapas aati.

Recall Predict

departments table mein wo rows hain jo students.dept_id se reference hoti hain. Tum DROP TABLE departments; run karte ho. Kya hota hai?

Answer: Ye fail hota hai (ya CASCADE chahiye) kyunki ek foreign key abhi bhi usse depend karti hai. DB referential integrity protect karta hai. DROP TABLE departments CASCADE; ise force kar deta aur dependent constraint bhi drop kar deta.

Flashcards

What does DDL stand for and what does it manage?

Data Definition Language; ye schema objects (tables, indexes, views) ko define/modify/remove karta hai, yaani structure, na ki row data.

Why are most DDL statements considered dangerous?

Ye auto-commit hote hain aur usually roll back nahi kiye ja sakte, isliye DROP/TRUNCATE immediate permanent effect lete hain.

PRIMARY KEY equals which two constraints combined?

UNIQUE + NOT NULL.

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

CHAR fixed n length tak pad karta hai; VARCHAR actual length store karta hai max n tak.

What does FOREIGN KEY enforce?

Referential integrity — value referenced table ke column mein exist karni chahiye.

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

Ek DEFAULT, taaki existing rows ko ek valid value mile aur NOT NULL violate na ho.

TRUNCATE vs DELETE: name 3 differences.

TRUNCATE mein WHERE nahi hota, faster hai (pages deallocate karta hai), identity reset karta hai, usually non-rollback; DELETE filter karta hai, slower/logged hai, rollback-able hai, DML hai.

Is TRUNCATE rollback-able in InnoDB?

Nahi — ye DDL ki tarah auto-commits karta hai.

Does ALTER ... DROP COLUMN delete the table?

Nahi, sirf wo ek column remove hota hai; table survive karti hai.

Why use DROP TABLE IF EXISTS in scripts?

Table already absent ho to error avoid karne ke liye, script idempotent ban jaati hai.

Which DDL verb keeps structure but empties all data fastest?

TRUNCATE.

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

Dependency error; CASCADE chahiye ya pehle FK drop karo.

Recall Feynman: explain to a 12-year-old

Socho tumhara data ek toy box hai.

CREATE = bilkul nayi empty box banana with labelled sections ("cars yahaan, blocks wahaan").
ALTER = box mein ek nayi section add karna ya koi label rename karna, bina apne toys phenke.
DROP = pura box dustbin mein phenk dena — toys, labels, sab kuch.
TRUNCATE = saare toys bahar dump kar dena lekin empty box ko uske labels ke saath rakhna, refill ke liye ready. Aur jab CREATE/DROP/TRUNCATE ho jaaye, tum "undo" nahi bol sakte — ye permanent ho chuka hai.

Connections

SQL DML — INSERT, UPDATE, DELETE, SELECT — wo data jo DDL structures ke andar rehta hai
Constraints — PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE — CREATE/ALTER ke andar declare kiye gaye rules
Normalization — kyun data multiple CREATE-d tables mein split karte hain
Transactions and ACID — kyun DDL auto-commit matter karta hai
Indexes — CREATE/DROP INDEX queries speed up karta hai
Referential Integrity & CASCADE — kyun DROP fail ho sakta hai