MySQL DDL Table Commands — CREATE, ALTER, DROP & DESCRIBE (2026)

Introduction#

Why This Matters#

Every piece of data in a relational database lives in a table. Before you can insert a single record, you must define the table's structure — its columns, data types, sizes, and constraints. DDL (Data Definition Language) table commands are the tools you use to create, modify, and remove that structure.

These commands are irreversible in the sense that DROP TABLE destroys data permanently and ALTER TABLE can lock large tables during modification. Understanding the syntax and consequences of each command is critical for building reliable, maintainable database schemas.

MySQL Data Types Reference#

Choosing the right data type is the first decision in table design:

CREATE TABLE — Defining Table Structure#

CREATE TABLE tableName (
    fieldname datatype(size) constraint,
    fieldname datatype(size) constraint,
    ...
);

Constraints enforce data rules at the database level:

• PRIMARY KEY — uniquely identifies each row; no NULLs, no duplicates
• NOT NULL — the column must always have a value
• UNIQUE — all values in the column must be different (NULLs allowed)
• DEFAULT value — provides a fallback when no value is supplied

-- Students table with a primary key and constraints
CREATE TABLE students (
    admno    INT(5)       PRIMARY KEY,
    name     VARCHAR(30)  NOT NULL,
    class    VARCHAR(10),
    dob      DATE,
    fees     DECIMAL(8,2) DEFAULT 0.00
);

-- Library table using AUTO_INCREMENT for surrogate key
CREATE TABLE library (
    bookNo   INT          AUTO_INCREMENT PRIMARY KEY,
    bookName VARCHAR(50)  NOT NULL,
    author   VARCHAR(40),
    price    DECIMAL(7,2)
);

AUTO_INCREMENT automatically assigns the next integer when a row is inserted without specifying the primary key.

ALTER TABLE — Modifying Existing Tables#

ALTER TABLE lets you change a table's structure after it has been created, even when it already contains data.

ADD — Adding a New Column#

ALTER TABLE students ADD address VARCHAR(30);
 
-- Add the column after an existing column
ALTER TABLE students ADD phone VARCHAR(15) AFTER name;

ADD PRIMARY KEY — Adding a Primary Key#

Use this when you forgot to set the primary key during CREATE TABLE:

ALTER TABLE library ADD PRIMARY KEY (bookNo);

DROP COLUMN — Removing a Column#

ALTER TABLE students DROP address;

All data in the dropped column is permanently lost.

DROP PRIMARY KEY — Removing the Primary Key#

ALTER TABLE students DROP PRIMARY KEY;

Note: if the primary key column is AUTO_INCREMENT, you must first remove AUTO_INCREMENT with a MODIFY before dropping the key.

MODIFY — Changing a Column's Data Type or Constraint#

-- Change bookName to a larger size
ALTER TABLE library MODIFY bookName VARCHAR(80);
 
-- Make a column NOT NULL
ALTER TABLE library MODIFY bookName VARCHAR(80) NOT NULL;
 
-- Allow NULLs again
ALTER TABLE library MODIFY bookName VARCHAR(80) NULL;

RENAME COLUMN — Renaming a Column (MySQL 8.0+)#

ALTER TABLE students RENAME COLUMN admno TO admission_number;

Multiple Changes in One Statement#

ALTER TABLE students
    ADD email VARCHAR(60),
    MODIFY fees DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    DROP phone;

Combining changes reduces the number of table rebuilds.

DROP TABLE — Deleting a Table#

DROP TABLE removes the table definition and all its rows permanently:

DROP TABLE students;
 
-- Safe version — no error if table does not exist
DROP TABLE IF EXISTS students;

Use TRUNCATE TABLE if you want to keep the structure but delete all rows quickly:

TRUNCATE TABLE students;
-- Faster than DELETE FROM students; — no WHERE clause, resets AUTO_INCREMENT

SHOW TABLES — Listing Tables in the Current Database#

SHOW TABLES;

To see tables in a specific database without switching to it:

SHOW TABLES FROM school_mgmt;

DESC / DESCRIBE — Inspecting Table Structure#

DESC (shorthand for DESCRIBE) shows the column names, data types, nullability, key info, and defaults for a table:

DESC students;

Sample output:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| admno | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| class | varchar(10) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
| fees  | decimal(8,2)| YES  |     | 0.00    |       |
+-------+-------------+------+-----+---------+-------+

For more detail including indexes and foreign keys use SHOW CREATE TABLE students;.

Common Mistakes#

• Using ALTER TABLE to modify data values. ALTER TABLE changes structure; use UPDATE to change data.
• Dropping a column referenced by a foreign key. MySQL will raise an error — drop the foreign key constraint first.
• Choosing CHAR for variable-length data. CHAR(50) always uses 50 bytes; VARCHAR(50) uses only as many bytes as the stored string plus 1-2 bytes for length.
• Forgetting NOT NULL on required fields. Missing entries silently default to NULL, causing logic bugs later.
• Rebuilding large tables with ALTER TABLE during peak hours. On tables with millions of rows, ALTER TABLE may lock the table; use pt-online-schema-change or MySQL 8.0 instant DDL.

Best Practices#

• Always define a PRIMARY KEY on every table; MySQL stores InnoDB tables clustered by primary key.
• Use VARCHAR for names and descriptions; reserve CHAR for fixed-length codes like country codes (CHAR(2)).
• Use DECIMAL for monetary values — never FLOAT or DOUBLE, which have rounding errors.
• Add NOT NULL DEFAULT clauses to prevent accidental NULLs and simplify application logic.
• Run DESC tablename or SHOW CREATE TABLE tablename after every ALTER TABLE to verify the change.
• Script all schema changes in migration files (using tools like Flyway or Liquibase) so they are version-controlled and repeatable.

Key Takeaways#

• CREATE TABLE defines columns with data types, sizes, and optional constraints like PRIMARY KEY, NOT NULL, and UNIQUE.
• ALTER TABLE modifies existing table structure; changes include adding columns, dropping columns, and changing data types.
• DROP TABLE permanently deletes the table and all its data; TRUNCATE TABLE keeps the structure but removes all rows.
• SHOW TABLES lists all tables in the active database; SHOW TABLES FROM dbname works without switching context.
• DESC tablename or DESCRIBE tablename displays the column structure, types, key info, and default values.
• VARCHAR stores variable-length strings and saves space compared to CHAR, which always uses the declared fixed length.
• DECIMAL(p,s) stores exact decimal numbers and is the correct choice for prices and financial figures.
• Multiple ALTER TABLE sub-commands can be chained in one statement to reduce the number of table rebuilds.