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

This page assumes you have seen nothing. We name every word, notation, and symbol the parent note leans on, draw the picture behind it, and say why the topic can't work without it. Start at line one.

0. What is a database, really?

Before any SQL word makes sense, you need the picture the words describe.

Look at the figure: the grey grid is the table students. The columns are the labelled headers running left-to-right (id, name, age). Each row is one filled-in line. DDL never touches individual rows-as-data — it decides which columns exist and what rules they obey. That is the drawer's shape, not the papers inside it.

1. SQL and the semicolon ;

You'll see ; at the end of every command in the parent note. It's not decoration: it tells the database "this thought is finished, run it now."

2. The keyword vs the name

Every DDL command mixes two kinds of words. Telling them apart is the reading skill the whole topic needs.

By convention keywords are written in UPPERCASE and your names in lowercase, so your eye can separate them instantly.

3. A data type — the "kind of value" a column holds

The parent note uses four types. Here is the plain-words picture of each:

The figure shows the crucial CHAR vs VARCHAR difference the parent's "steel-man" warns about. Storing "IN" in CHAR(5) uses all 5 slots (padded ▯▯), while VARCHAR(5) uses only 2. That is why the "VARCHAR always uses n bytes" belief is wrong.

4. NULL — the "no value here" marker

You need NULL to understand the constraint NOT NULL, which we meet next.

5. Constraints — the rules a column must obey

A constraint is a rule that rejects illegal rows automatically. This is the heart of a good CREATE statement, so we build each rule from zero.

The figure draws the FOREIGN KEY picture, which the parent's DROP/CASCADE examples depend on. The arrow from students.dept_id points to departments.id: a student may only name a department that actually exists. This arrow is called referential integrity — see Referential Integrity & CASCADE. The arrow is why dropping departments fails while students still point at it.

5.1 The comparison symbols inside CHECK

The parent writes CHECK (age >= 0) and CHECK (age < 130). Two symbols do the work:

These are the only math symbols the parent note uses, and they answer the question "is this value in the allowed range?" — the exact job a CHECK constraint needs.

6. DEFAULT and CURRENT_DATE

This pair is exactly why the parent's "add a NOT NULL column" example needs a DEFAULT: existing rows have no value, so a default must plug the hole or the ALTER fails.

7. auto-commit, COMMIT, ROLLBACK

The parent note's warnings ("can't roll back", "auto-commits") use three transaction words. Here they are from zero — deeper in Transactions and ACID.

8. The prerequisite map

Read it top-down: the cabinet/drawer picture feeds columns and rows; columns need types; types need the NULL marker; constraints build on NULL; keys link tables; and the transaction words explain why the four DDL verbs are permanent. Everything funnels into the box marked CREATE ALTER DROP TRUNCATE.

Equipment checklist

Test yourself — cover the right side and answer aloud.