Mastering SQL DDL: CREATE, ALTER, DROP & More

INTRODUCTION

If you’ve ever worked with a relational database, you’ve almost certainly used SQL DDL — even if you didn’t know it by that name. DDL stands for Data Definition Language, and it forms the structural backbone of every database-driven application.

While SQL is broadly divided into several sub-languages — DML (Data Manipulation Language), DCL (Data Control Language), TCL (Transaction Control Language) — DDL is special. It’s the part of SQL that lets you define, modify, and manage the schema: the blueprint of your database.

In this guide, we’ll break down all the major DDL commands with clear explanations, real-world examples, and best practices to keep you writing clean, efficient SQL.

WHAT IS DDL?

DDL (Data Definition Language) refers to a set of SQL commands used to define and manage the structure of database objects. Unlike DML which handles data rows, DDL deals with the containers that hold data: tables, indexes, schemas, views, sequences, and more.

Key characteristics of DDL commands:

• They affect the database schema, not the data itself
• They are auto-committed in most databases (changes are permanent immediately)
• They can invalidate dependent objects like views or stored procedures
• They require elevated privileges to execute

1. CREATE

The CREATE command is where every database story begins. It lets you bring new objects into existence — from entire schemas to individual tables, indexes, and views.

CREATE DATABASE

Before creating tables, you need a database to put them in:

CREATE DATABASE company_db;

CREATE TABLE

The most commonly used DDL command. It defines a new table along with its columns, data types, and constraints:

CREATE TABLE employees (
    employee_id   INT           PRIMARY KEY AUTO_INCREMENT,
    first_name    VARCHAR(50)   NOT NULL,
    last_name     VARCHAR(50)   NOT NULL,
    email         VARCHAR(100)  UNIQUE NOT NULL,
    department_id INT,
    salary        DECIMAL(10,2) DEFAULT 0.00,
    hire_date     DATE          NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(dept_id)
);

Let’s unpack what’s happening here:

• PRIMARY KEY AUTO_INCREMENT — uniquely identifies each row and auto-generates the value
• NOT NULL — ensures the column cannot be left empty
• UNIQUE — prevents duplicate values in the email column
• DEFAULT 0.00 — sets a default value when none is provided
• FOREIGN KEY — enforces referential integrity between tables

CREATE INDEX

Indexes dramatically speed up data retrieval. Use them on columns you frequently filter or join on:

-- Single-column index
CREATE INDEX idx_employee_email
ON employees(email);
-- Composite index for multi-column queries
CREATE INDEX idx_dept_salary
ON employees(department_id, salary);

CREATE VIEW

A view is a named query stored in the database. It simplifies complex queries and can restrict data access:

CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 80000;

2. ALTER

As your application evolves, so must your database schema. The ALTER command lets you make changes to existing objects without dropping and recreating them. It’s the DDL command you’ll use most frequently after initial setup.

ADD a Column

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20);

MODIFY a Column

Change the data type, size, or nullability of an existing column:

-- MySQL / MariaDB syntax
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2) NOT NULL;
-- PostgreSQL / SQL Server syntax
ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12,2);

DROP a Column

ALTER TABLE employees
DROP COLUMN phone_number;

ADD a Constraint

-- Add a CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);
-- Add a FOREIGN KEY
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (department_id) REFERENCES departments(dept_id);

DROP a Constraint

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

3. DROP

The DROP command permanently deletes a database object — table, view, index, schema, database — along with all its data. There is no undo. Use it with extreme caution.

DROP TABLE

DROP TABLE employees;

DROP TABLE IF EXISTS

A safer variant that suppresses the error if the table doesn’t exist — very useful in migration scripts:

DROP TABLE IF EXISTS employees;

DROP with CASCADE

When a table is referenced by foreign keys in other tables, you need CASCADE to remove it along with those dependencies:

-- PostgreSQL
DROP TABLE departments CASCADE;

DROP DATABASE

DROP DATABASE company_db;

DROP INDEX

-- MySQL
DROP INDEX idx_employee_email ON employees;
-- PostgreSQL / SQL Server
DROP INDEX idx_employee_email;

4. TRUNCATE

TRUNCATE removes all rows from a table instantly, without logging individual row deletions. It’s much faster than DELETE for clearing large tables, but it comes with important caveats.

TRUNCATE TABLE employees;

TRUNCATE vs DELETE — Key Differences

5. RENAME

The RENAME command changes the name of an existing database object. Syntax varies slightly across database platforms.

MySQL

RENAME TABLE employees TO staff;

PostgreSQL

ALTER TABLE employees RENAME TO staff;

Oracle

RENAME employees TO staff;

Rename a Column

-- MySQL 8.0+ and PostgreSQL
ALTER TABLE employees
RENAME COLUMN hire_date TO start_date;

CONSTRAINTS IN DDL

Constraints are rules enforced at the database level to ensure data integrity. They are defined as part of DDL and are one of the most important features of a well-designed schema.

CREATE TABLE orders (
    order_id    INT           PRIMARY KEY AUTO_INCREMENT,
    customer_id INT           NOT NULL,
    order_date  DATE          NOT NULL DEFAULT (CURRENT_DATE),
    status      VARCHAR(20)   CHECK (status IN ('pending','shipped','delivered')),
    total       DECIMAL(10,2) CHECK (total >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Summary of constraint types:

• PRIMARY KEY — uniquely identifies each row, implies NOT NULL + UNIQUE
• FOREIGN KEY — links to a row in another table (referential integrity)
• UNIQUE — no two rows can have the same value in this column
• NOT NULL — the column must always have a value
• CHECK — custom condition the value must satisfy
• DEFAULT — value to use when none is provided

BEST PRACTICES

• Always use IF EXISTS / IF NOT EXISTS in scripts to prevent failures on re-runs
• Test DDL changes in a staging environment before applying to production
• Use database migrations (Flyway, Liquibase, Alembic) to version-control schema changes
• Document schema changes in a CHANGELOG.md alongside your code
• Index foreign key columns to improve JOIN performance
• Prefer TRUNCATE over DELETE for bulk clears when rollback is not needed
• Schedule heavy ALTER operations during low-traffic periods
• Be explicit with data types — avoid overly generic types like TEXT when VARCHAR suffices

SUMMARY

SQL DDL is the foundation that every database is built on. Whether you’re bootstrapping a new project or evolving a decades-old system, a solid understanding of CREATE, ALTER, DROP, TRUNCATE, and RENAME will serve you well.

The key takeaways from this guide:

• DDL defines and manages the structure of database objects
• Most DDL commands are auto-committed and irreversible
• Constraints are your first line of defense for data integrity
• Always test schema changes in a safe environment first
• Use migration tools to bring discipline to schema evolution

Mastering DDL means you’re thinking not just about the data, but about the blueprint that makes that data reliable, consistent, and queryable at scale.