Data Definition Language builds the shape of your database. Design a table column by column, watch the CREATE TABLE statement write itself, then ALTER, TRUNCATE and DROP it to see what happens to the data each time.
Before a single row can be stored, something has to declare what a row looks like. That is DDL's job.
Data Definition Language is the subset of SQL that creates and changes structure — tables, columns, types, constraints, indexes. It is distinct from DML, which changes the contents. A useful shorthand: DDL is the blueprint, DML is the furniture.
In most engines (MySQL and Oracle in particular) DDL statements perform an implicit commit: the moment you run one, any open transaction is committed and the change is permanent. A ROLLBACK afterwards will not save you. PostgreSQL is the notable exception — it supports transactional DDL.
This is why DROP TABLE on the wrong connection is one of the most feared mistakes in this profession. Press the DROP button here and note that the row counter goes to zero with no undo offered.
Constraints declared in DDL are checked on every write, forever, no matter which application is connecting:
Putting a rule here rather than in application code means it cannot be bypassed by a script, a migration or a careless intern with a database client.
SQL splits into families. DML moves data around — SELECT, INSERT, UPDATE, DELETE. DDL defines the containers that data lives in, and there are four commands worth knowing properly.
CREATE makes something: a table, an index, a view, a schema.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
dept_id INT REFERENCES departments(id),
salary DECIMAL(10,2) CHECK (salary > 0),
hired_on DATE NOT NULL DEFAULT CURRENT_DATE
);ALTER changes it afterwards:
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR(150);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees ADD CONSTRAINT salary_cap CHECK (salary < 1000000);DROP removes the object and everything in it. TRUNCATE empties a table but keeps its structure.
They get confused constantly, and the difference matters at exactly the moment you cannot undo it.
| What it removes | Speed | Rollback | Triggers fire | Resets identity | |
|---|---|---|---|---|---|
DELETE FROM t | Rows, one at a time | Slow on big tables | Yes | Yes | No |
TRUNCATE TABLE t | All rows, at once | Very fast | In PostgreSQL yes; MySQL no | No | Usually yes |
DROP TABLE t | The table itself | Fast | Depends on engine | No | N/A |
DELETE is the only one that can be given a WHERE clause. TRUNCATE deallocates the storage rather than removing rows individually, which is why it is orders of magnitude faster and why it cannot fire row-level triggers. DROP leaves nothing behind — no structure, no indexes, no permissions.
The critical operational point: in MySQL and Oracle, DDL causes an implicit commit. Any open transaction is committed before the DDL runs, and the DDL itself cannot be rolled back. In PostgreSQL and SQL Server, DDL is transactional — you can BEGIN, alter a table, decide against it, and ROLLBACK.
That difference changes how you write migrations. On PostgreSQL you can wrap a multi-step schema change in a transaction and get all-or-nothing. On MySQL you cannot, so each step has to be individually safe and reversible by hand.
Constraints are the reason a database is more than a file format. Each one is a rule the engine will not let any client break, including the buggy one you deploy at 5pm on a Friday.
PRIMARY KEY — unique and not null; the row's identity.FOREIGN KEY — must match a row in another table, or be NULL. Add ON DELETE CASCADE to remove children automatically, or ON DELETE RESTRICT to refuse the parent's deletion.UNIQUE — no duplicates. Allows multiple NULLs in most engines, since NULLs are not equal to each other.NOT NULL — a value is required.CHECK — an arbitrary condition, such as CHECK (end_date > start_date). Note it passes when the condition is UNKNOWN, so a NULL slips through unless you also declare NOT NULL.DEFAULT — not strictly a constraint, but the same spirit: a value supplied when none is given.Enforcing these in application code instead is the most common and most expensive schema mistake. There is always a second writer — a migration, a script, an admin console, a colleague's notebook — and it will not have your validation logic.
DDL defines what a table is, and every constraint you declare is a rule the database enforces for every writer forever -- including the ones written after you leave. This creates a table, then tries to break each rule in turn.
DDL defines the contract your data must obey. Time spent choosing sensible types and constraints up front is repaid every day afterwards — and because these statements are usually irreversible, they deserve more care than any query you will write.
On a small table, ALTER TABLE is instant. On a large, busy one it can lock the table and take the application down, so the details matter.
CREATE INDEX CONCURRENTLY (PostgreSQL) or an online index build.NOT NULL constraint requires scanning the table to verify. Adding it as NOT VALID and validating later avoids the long lock in PostgreSQL.The safe pattern for a risky change is the expand–migrate–contract sequence: add the new column, write to both old and new for a period, backfill in batches, switch readers over, then drop the old column in a later release. It takes three deployments instead of one, and it is how schema changes happen on systems that cannot go down.
Always set a short lock_timeout before running DDL on a live system. A migration that waits behind a long-running query will queue every other query behind itself, turning a slow migration into an outage.
Can I roll back a DROP TABLE? In PostgreSQL, yes, if it was inside a transaction that you roll back. In MySQL, no. Either way, backups are the real answer.
What is the difference between SERIAL and IDENTITY? SERIAL is PostgreSQL's older shorthand that creates a sequence behind the scenes; GENERATED ALWAYS AS IDENTITY is the standard-compliant successor and is preferred in new schemas.
Should I use VARCHAR(255)? Only if 255 is a real limit. The number is an inherited habit from old MySQL row formats; in PostgreSQL there is no performance difference between VARCHAR(255) and TEXT.
Do constraints slow down writes? Slightly — each one is a check. Foreign keys also require an index lookup on the parent. The cost is small and the protection is large.
Can I add a foreign key to an existing table? Yes, and the engine validates every existing row first. On big tables use the NOT VALID then VALIDATE CONSTRAINT two-step where supported.
How do I version schema changes? With migration files in source control, applied in order and recorded in a table — Flyway, Liquibase, Alembic, or your framework's own tool. Manual DDL on production is how environments drift apart.
CREATE, ALTER, DROP, TRUNCATE.DELETE removes rows and can be filtered; TRUNCATE empties fast; DROP removes the object entirely.About to run: Press CREATE, then ALTER · ADD COLUMN. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What is meant by “Adding a column with a default” here?
historically rewrote every row. PostgreSQL 11+ and MySQL 8+ handle constant defaults without a rewrite; older versions do not.
What is meant by “Changing a column type” here?
generally rewrites the table and holds a strong lock.
What is meant by “Adding an index” here?
locks writes unless you use CREATE INDEX CONCURRENTLY (PostgreSQL) or an online index build.
What is meant by “Adding a NOT NULL constraint” here?
requires scanning the table to verify. Adding it as NOT VALID and validating later avoids the long lock in PostgreSQL.
Data Definition Language builds the shape of your database. Design a table column by column, watch the CREATE TABLE statement write itself, then ALTER, TRUNCATE and DROP it to see what happens to the data each time.