DDL Commands in SQL: CREATE, ALTER, DROP, TRUNCATE and RENAME (with Examples)

DDL (Data Definition Language) commands create and change the structure of a database — tables, columns and indexes — rather than the data inside them. The main DDL commands are CREATE, ALTER, DROP, TRUNCATE and RENAME. Testers use them to build test tables, check schema changes and reset data between test runs.

SQL Command Types: DDL vs DML vs DCL vs TCL

Every example below was run on MariaDB 10.11, which uses the same syntax as MySQL for these commands.

CREATE: Make a Table

CREATE DATABASE qa_demo;
USE qa_demo;

CREATE TABLE test_runs (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  test_name  VARCHAR(100) NOT NULL,
  status     VARCHAR(10)  NOT NULL DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

DESCRIBE test_runs;

Output

+------------+--------------+------+-----+---------------------+----------------+
| Field      | Type         | Null | Key | Default             | Extra          |
+------------+--------------+------+-----+---------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL                | auto_increment |
| test_name  | varchar(100) | NO   |     | NULL                |                |
| status     | varchar(10)  | NO   |     | pending             |                |
| created_at | datetime     | YES  |     | current_timestamp() |                |
+------------+--------------+------+-----+---------------------+----------------+

ALTER: Change a Table

ALTER TABLE test_runs ADD COLUMN browser VARCHAR(20) AFTER test_name;      -- add a column
ALTER TABLE test_runs MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';  -- change a column
ALTER TABLE test_runs RENAME COLUMN browser TO browser_name;            -- rename a column
CREATE INDEX idx_status ON test_runs (status);                           -- add an index

DESCRIBE test_runs;

Output

+--------------+--------------+------+-----+---------------------+----------------+
| Field        | Type         | Null | Key | Default             | Extra          |
+--------------+--------------+------+-----+---------------------+----------------+
| id           | int(11)      | NO   | PRI | NULL                | auto_increment |
| test_name    | varchar(100) | NO   |     | NULL                |                |
| browser_name | varchar(20)  | YES  |     | NULL                |                |
| status       | varchar(20)  | NO   | MUL | pending             |                |
| created_at   | datetime     | YES  |     | current_timestamp() |                |
+--------------+--------------+------+-----+---------------------+----------------+

The new browser_name column appears after test_name, status is now varchar(20), and MUL in the Key column shows the new index.

RENAME: Rename a Table

RENAME TABLE test_runs TO automation_runs;
-- same as: ALTER TABLE test_runs RENAME TO automation_runs;

TRUNCATE vs DELETE vs DROP

All three remove data, but very differently — and the difference catches testers out. This was run with 2 rows in the table each time:

START TRANSACTION;
TRUNCATE TABLE automation_runs;
ROLLBACK;
SELECT COUNT(*) FROM automation_runs;   -- 0  (TRUNCATE commits immediately)

START TRANSACTION;
DELETE FROM automation_runs;
ROLLBACK;
SELECT COUNT(*) FROM automation_runs;   -- 2  (DELETE was undone)

DROP: Delete a Table

DROP TABLE automation_runs;
DROP TABLE IF EXISTS automation_runs;   -- no error if it's already gone

DDL Checks Testers Run After a Release

• DESCRIBE table_name; or SHOW CREATE TABLE table_name; — did the new column, type, default and NOT NULL arrive as specified?
• SHOW INDEX FROM table_name; — is the index the developers promised there?
• Insert boundary data into a changed column (maximum length, NULL, default) and check what the database does.
• Run the migration twice on a copy — scripts using IF NOT EXISTS / IF EXISTS shouldn't fail.

Common Mistakes

1. Using TRUNCATE in a test-data reset and expecting ROLLBACK to undo it. In MySQL/MariaDB it commits immediately. Use DELETE inside a transaction if you need to undo.
2. Running DDL on the wrong database. Check with SELECT DATABASE(); before any DROP or TRUNCATE.
3. Shrinking a column (e.g. VARCHAR(100) → VARCHAR(20)) on a table that already has longer values — depending on SQL mode the change fails or data is cut. Check MAX(CHAR_LENGTH(col)) first.
4. Forgetting IF EXISTS in clean-up scripts, so they fail when run twice.

Practice Exercises

1. Create a bugs table with id, title, severity and created_at.
2. Add a priority column, then rename severity to bug_severity.
3. Insert 3 rows, then show that DELETE can be rolled back but TRUNCATE cannot.

Key Takeaways

• DDL changes structure: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• DML changes data: INSERT, UPDATE, DELETE.
• TRUNCATE and other DDL commit automatically in MySQL/MariaDB — they can't be rolled back.

📚 Official documentation: MySQL manual: Data Definition Statements · MariaDB: Data Definition

Frequently Asked Questions

What are DDL commands in SQL?

Commands that define or change database structure: CREATE, ALTER, DROP, TRUNCATE and RENAME.

Is TRUNCATE DDL or DML?

DDL. It removes all rows by recreating the table storage, resets AUTO_INCREMENT and, in MySQL/MariaDB, commits automatically.

Can DDL commands be rolled back?

Not in MySQL or MariaDB — DDL statements cause an implicit commit. Some databases, such as PostgreSQL, do support transactional DDL.

What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows but keeps the table. DROP removes the table itself, including its structure.

Is SELECT a DDL command?

No. SELECT reads data; it is usually grouped with DML or called DQL (Data Query Language).