Home/Learn/MySQL/DDL — CREATE, ALTER, DROP

DDL — CREATE, ALTER, DROP

Beginner

Fundamentals

Data Definition Language defines schema; CREATE TABLE, ALTER TABLE (add/modify columns), DROP TABLE, and TRUNCATE are non-transactional DDL statements in MySQL.

Overview

DDL (Data Definition Language) statements define and modify database schema. In MySQL, DDL statements are auto-committed and cannot be rolled back. Key DDL operations: CREATE TABLE (define columns, data types, constraints, indexes), ALTER TABLE (add/modify/drop columns or indexes, change engine), DROP TABLE / TRUNCATE TABLE (remove table or clear rows), and CREATE/DROP INDEX. Online DDL (MySQL 5.6+ InnoDB) allows most ALTER TABLE operations without a full table lock, though some operations still require a rebuild.

CREATE TABLE

A well-designed CREATE TABLE specifies the right data types, NOT NULL constraints, a primary key, and only the indexes needed. Avoid over-indexing at creation — add indexes based on actual query patterns.

SQL — CREATE TABLE with constraints and indexes

CREATE TABLE orders (
    id            BIGINT          NOT NULL AUTO_INCREMENT,
    customer_id   BIGINT          NOT NULL,
    status        ENUM('DRAFT','PLACED','SHIPPED','DELIVERED','CANCELLED')
                                  NOT NULL DEFAULT 'DRAFT',
    total         DECIMAL(10, 2)  NOT NULL,
    notes         TEXT,                          -- nullable TEXT column
    created_at    DATETIME(3)     NOT NULL       -- millisecond precision
                                  DEFAULT CURRENT_TIMESTAMP(3),
    updated_at    DATETIME(3)     NOT NULL
                                  DEFAULT CURRENT_TIMESTAMP(3)
                                  ON UPDATE CURRENT_TIMESTAMP(3),

    PRIMARY KEY (id),
    INDEX idx_customer_status (customer_id, status),   -- composite index
    INDEX idx_created_at (created_at),

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

ALTER TABLE

ALTER TABLE modifies an existing table. InnoDB Online DDL minimises locking for most changes. Use ALGORITHM=INPLACE, LOCK=NONE to request an online operation and get an error if MySQL cannot do it lock-free.

SQL — ALTER TABLE with online DDL hints

-- Add a column (INSTANT algorithm — zero-copy in MySQL 8.0+)
ALTER TABLE orders
    ADD COLUMN carrier VARCHAR(50) NULL,
    ALGORITHM=INSTANT;   -- fastest — just adds metadata, no rebuild

-- Modify column type (requires REBUILD — causes brief exclusive lock)
ALTER TABLE orders
    MODIFY COLUMN notes VARCHAR(1000) NULL,
    ALGORITHM=INPLACE, LOCK=NONE;   -- error if MySQL needs a lock

-- Add index online (no table lock)
ALTER TABLE orders
    ADD INDEX idx_status_created (status, created_at),
    ALGORITHM=INPLACE, LOCK=NONE;

-- Drop column
ALTER TABLE orders DROP COLUMN notes, ALGORITHM=INPLACE, LOCK=NONE;

-- Rename column (MySQL 8.0+)
ALTER TABLE orders RENAME COLUMN carrier TO shipping_carrier;

-- Change storage engine (rebuilds table — avoid on large tables)
ALTER TABLE old_table ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

DROP, TRUNCATE & Constraints

DROP TABLE removes the table and all data. TRUNCATE is DDL (not DML) — it resets AUTO_INCREMENT and is faster than DELETE but cannot be rolled back. Foreign key constraints block DROP on referenced tables.

SQL — DROP, TRUNCATE, and batch DELETE

-- TRUNCATE — resets AUTO_INCREMENT, non-transactional, very fast
TRUNCATE TABLE order_items;

-- DROP TABLE with IF EXISTS (avoids error in scripts)
DROP TABLE IF EXISTS temp_migration;

-- Disable FK checks temporarily during bulk migrations
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS order_items;   -- would fail with FK checks on
DROP TABLE IF EXISTS orders;
SET foreign_key_checks = 1;

-- TRUNCATE vs DELETE comparison:
-- TRUNCATE: DDL, cannot rollback, resets AUTO_INCREMENT, no triggers, fast
-- DELETE:   DML, can rollback, keeps AUTO_INCREMENT, fires triggers, slow for large tables

-- Safe large-table deletion (avoid DELETE without LIMIT)
DELETE FROM events
WHERE created_at < NOW() - INTERVAL 90 DAY
LIMIT 10000;                        -- delete in batches to avoid long lock
-- Repeat in a loop until 0 rows affected

Key Points to Remember

1DDL statements (CREATE, ALTER, DROP, TRUNCATE) are auto-committed and cannot be rolled back.
2MySQL 8.0 INSTANT algorithm adds columns to InnoDB tables without a table copy.
3ALGORITHM=INPLACE, LOCK=NONE requests an online DDL with an error if unsupported.
4TRUNCATE is faster than DELETE * for clearing tables but resets AUTO_INCREMENT.
5Foreign key constraints prevent DROP TABLE on referenced tables — use foreign_key_checks=0 during migrations.
6Always use batch DELETE with LIMIT to avoid long-running locks on large tables.

Interview Questions

What is the difference between TRUNCATE and DELETE?

EasyInfosys

Why can't DDL statements be rolled back in MySQL?

EasyTCS

What is Online DDL in MySQL and which ALTER TABLE operations support it?

MediumAmazon

What does ALGORITHM=INSTANT mean for ALTER TABLE in MySQL 8?

MediumBooking.com

How would you safely delete millions of rows without causing a lock timeout?

HardLinkedIn

Ask Aria about DDL — CREATE, ALTER, DROP

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

MySQL Data Types

DML — INSERT, UPDATE, DELETE