DDL

Definition

DDL (Data Definition Language) is the family of SQL statements that define or change the structure of database objects — tables, views, indexes, constraints — as opposed to the data stored inside them. The three core DDL verbs are CREATE, ALTER, and DROP.

How It Works

sql
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50),
  salary INT
);

ALTER TABLE employees ADD COLUMN hire_date DATE;   -- change structure
ALTER TABLE employees DROP COLUMN department;       -- remove a column

DROP TABLE employees;                               -- remove the whole object

A defining, often-surprising trait of DDL: in most mainstream engines (MySQL, Oracle, SQL Server), DDL statements auto-commit — a CREATE TABLE or ALTER TABLE takes effect immediately and cannot be rolled back with a later ROLLBACK, unlike DML changes. PostgreSQL is a notable exception: it supports transactional DDL, where a CREATE/ALTER/DROP inside an explicit transaction genuinely can be rolled back.

Edge Cases and Pitfalls

• DROP TABLE deletes both the structure AND all the data inside it, irreversibly (barring a backup) — this is fundamentally different from DELETE FROM table, which empties the data but keeps the table structure.
• Assuming DDL can always be rolled back like DML is a dangerous, dialect-dependent assumption — true on PostgreSQL, false on MySQL/Oracle/SQL Server by default.
• ALTER TABLE ... DROP COLUMN on a large, live production table can be a slow, locking operation on some engines — it is not always the "instant" structural change it looks like syntactically.

Key Takeaways

• DDL = CREATE / ALTER / DROP — defines structure, not data content.
• DDL auto-commits (cannot be rolled back) on most engines; PostgreSQL is the notable exception with transactional DDL.
• DROP removes structure and data together and is irreversible without a backup; DELETE only removes data, keeping the structure.