SQL DDL Commands: CREATE, ALTER, DROP, TRUNCATE & RENAME

What is DDL? DDL stands for Data Definition Language — the SQL commands that define and modify database structure. DDL doesn't touch your data; it creates, alters, and removes the containers (tables, databases) that hold your data. Think of it as building the shelves before you put books on them.

1 CREATE

Top

The CREATE command builds new database objects. We'll use it throughout all 5 topics in this chapter, building an employees table step by step.

CREATE DATABASE

SQL

CREATE DATABASE company_db;
USE company_db;

CREATE TABLE with Constraints

Here's a real-world table with all the important data types and constraints:

SQL

CREATE TABLE employees (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(150)  UNIQUE NOT NULL,
    department  VARCHAR(50)   DEFAULT 'General',
    salary      DECIMAL(10,2) CHECK (salary > 0),
    hire_date   DATE NOT NULL,
    is_active   BOOLEAN DEFAULT TRUE
);

Understanding Constraints

Constraint	What It Does	Example
`PRIMARY KEY`	Uniquely identifies each row, cannot be NULL	`id INT PRIMARY KEY`
`NOT NULL`	Column must have a value	`name VARCHAR(100) NOT NULL`
`UNIQUE`	No duplicate values allowed	`email VARCHAR(150) UNIQUE`
`DEFAULT`	Sets a value if none is provided	`department DEFAULT 'General'`
`CHECK`	Validates data against a condition	`CHECK (salary > 0)`
`FOREIGN KEY`	Links to another table's primary key	`FOREIGN KEY (dept_id) REFERENCES departments(id)`
`AUTO_INCREMENT`	Automatically generates sequential IDs	`id INT AUTO_INCREMENT`

Think of it this way: CREATE TABLE is like designing a spreadsheet template — you define the column headers and rules before any data goes in. Once created, the table enforces those rules on every row you INSERT.

Key Takeaways

CREATE DATABASE makes a new database; CREATE TABLE makes a new table
Always define a PRIMARY KEY — it uniquely identifies each row
Use constraints (NOT NULL, UNIQUE, CHECK) to enforce data quality at the database level
AUTO_INCREMENT generates IDs automatically so you don't have to

2 ALTER

Top

The ALTER command modifies an existing table's structure — adding columns, changing types, or updating constraints. Let's modify the employees table we just created.

Add a Column

SQL

-- Add a phone number column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

Modify a Column's Type

SQL

-- Increase name length from 100 to 150
ALTER TABLE employees
MODIFY COLUMN name VARCHAR(150) NOT NULL;

Drop a Column

SQL

-- Remove the phone column
ALTER TABLE employees
DROP COLUMN phone;

Add a Constraint

SQL

-- Ensure department values are unique
ALTER TABLE employees
ADD CONSTRAINT uq_email UNIQUE (email);

Rename a Column

SQL

-- Rename 'name' to 'full_name'
ALTER TABLE employees
RENAME COLUMN name TO full_name;

Production warning: Running ALTER TABLE on a large table with millions of rows can lock the table for minutes or longer, blocking all reads and writes. Always test changes on a copy first, and schedule schema changes during low-traffic windows.

Key Takeaways

ALTER TABLE ADD COLUMN adds new columns to an existing table
ALTER TABLE MODIFY COLUMN changes a column's data type or constraints
ALTER TABLE DROP COLUMN removes a column (and all its data)
Be cautious with ALTER on production tables — it can lock the table

3 DROP

Top

The DROP command permanently deletes a database object — the table, all its data, its indexes, its constraints, everything. There is no undo.

DROP TABLE

SQL

-- Delete the employees table completely
DROP TABLE employees;

DROP TABLE IF EXISTS

Prevents an error if the table doesn't exist:

SQL

-- Safe version - no error if table is missing
DROP TABLE IF EXISTS employees;

DROP DATABASE

SQL

-- Delete an entire database and everything in it
DROP DATABASE IF EXISTS test_db;

DROP INDEX

SQL

-- Remove an index (structure only, not data)
DROP INDEX idx_email ON employees;

There is no UNDO. DROP TABLE permanently destroys the table and all its data. Always back up before dropping anything in production. Use IF EXISTS to prevent errors in scripts.

Key Takeaways

DROP TABLE deletes the table AND all its data — permanently
Always use IF EXISTS to avoid errors when the object might not exist
DROP DATABASE deletes everything — all tables, data, indexes, procedures
DROP is irreversible — back up first, always

4 TRUNCATE

Top

TRUNCATE empties a table — it removes all rows but keeps the table structure intact. It's the "clear all" button.

SQL

-- Remove all employees but keep the table
TRUNCATE TABLE employees;

TRUNCATE vs DELETE: What's the Difference?

Both remove data, but they work very differently:

Feature	TRUNCATE	DELETE
Removes	All rows (no WHERE clause)	Specific rows or all rows
Speed	Very fast (doesn't log each row)	Slower (logs every deletion)
WHERE clause	Not supported	Supported — delete specific rows
AUTO_INCREMENT	Resets back to 1	Keeps the current value
Rollback	Cannot be rolled back (most DBs)	Can be rolled back in a transaction
Triggers	Does NOT fire triggers	Fires DELETE triggers
Type	DDL command	DML command

When to use which: Use TRUNCATE when you want to empty an entire table fast (e.g., clearing test data, resetting a staging table). Use DELETE when you need to remove specific rows or need the operation to be rollback-safe.

Key Takeaways

TRUNCATE removes all rows but preserves the table structure
It's faster than DELETE because it doesn't log individual row deletions
It resets AUTO_INCREMENT counters back to 1
It can't be rolled back and doesn't support WHERE — it's all or nothing

5 RENAME

Top

The RENAME command changes the name of a table. You can also rename columns using ALTER TABLE.

Rename a Table

SQL

-- MySQL syntax
RENAME TABLE employees TO staff;

-- Standard SQL / more portable
ALTER TABLE employees RENAME TO staff;

Rename a Column

SQL

-- Rename 'hire_date' to 'start_date'
ALTER TABLE staff
RENAME COLUMN hire_date TO start_date;

Rename Multiple Tables at Once

SQL

-- MySQL supports renaming multiple tables in one statement
RENAME TABLE
    old_customers TO customers_archive,
    new_customers TO customers;

Watch out: Renaming a table or column can break any queries, views, stored procedures, or application code that references the old name. Search your codebase for the old name before renaming.

Key Takeaways

RENAME TABLE old TO new changes a table's name (MySQL)
ALTER TABLE ... RENAME TO is the more portable syntax
ALTER TABLE ... RENAME COLUMN renames individual columns
Always check for dependent queries, views, and code before renaming

What's next? You now know how to define and modify database structures. Next up is DML Commands — where you'll learn to INSERT, SELECT, UPDATE, and DELETE actual data in your tables.

All Chapters in SQL Beginner Course

Continue Learning

Previous ChapterSQL Fundamentals Next Chapter DML Commands

DDL Commands

In This Chapter

1 CREATE

CREATE DATABASE

CREATE TABLE with Constraints

Understanding Constraints

Key Takeaways

2 ALTER

Add a Column

Modify a Column's Type

Drop a Column

Add a Constraint

Rename a Column

Key Takeaways

3 DROP

DROP TABLE

DROP TABLE IF EXISTS

DROP DATABASE

DROP INDEX

Key Takeaways

4 TRUNCATE

TRUNCATE vs DELETE: What's the Difference?

Key Takeaways

5 RENAME

Rename a Table

Rename a Column

Rename Multiple Tables at Once

Key Takeaways

All Chapters in SQL Beginner Course

Continue Learning

Courses

Services

Support

Connect