CREATE, ALTER, DROP Tables

What Is It?

Creating and Changing Table Structure

Real-world databases evolve. A table you designed last sprint will need a new column next sprint, an index the sprint after that, and a rename six months later when the product team decides "users" should be called "members". This chapter is the toolbox for all of those schema changes.

The commands covered here are collectively called DDL (Data Definition Language) — they define and change structure:

• CREATE TABLE — make a new table
• ALTER TABLE — add/drop/rename columns, add constraints, rename the table
• DROP TABLE — permanently delete a table and all its data
• TRUNCATE TABLE — delete every row (keep the table)
• CREATE TABLE AS SELECT — copy another table's rows into a new one
• CREATE TEMPORARY TABLE — a table that disappears when your session ends

The Sample Schema for This Chapter

Why Does It Matter?

Why Mastering DDL Matters

1. Every Project Has Migrations

A migration is a SQL script that evolves your schema from one version to another. Frameworks like Django, Laravel, Rails, and Flyway generate migrations automatically, but you still have to read and write ALTER TABLE statements. Knowing DDL fluently lets you review migrations before they hit production — and stops junior teammates from committing scripts that would drop a column used by reporting.

2. DROP vs TRUNCATE vs DELETE Is a Classic Interview Question

Asked in ~70% of SQL interviews. Many candidates know one or two of the three; knowing all three plus their trade-offs (speed, rollback behavior, AUTO_INCREMENT reset, FOREIGN KEY constraints) separates you from the crowd.

3. Bad ALTER Can Kill Production

An ALTER TABLE orders DROP COLUMN amount on a 500GB table can lock the table for 20 minutes, making your site unusable. Modern MySQL (8.0) has online DDL for many operations, but you still need to know which ALTER is safe and which needs a maintenance window. Professional teams use tools like pt-online-schema-change or gh-ost for big tables; this chapter teaches the core commands those tools wrap.

4. Making Scripts Re-Runnable (IF EXISTS / IF NOT EXISTS)

A script that errors on second run is a bug. Using IF NOT EXISTS on CREATE and IF EXISTS on DROP makes scripts idempotent — safe to run any number of times. This is basic hygiene every backend engineer is expected to know.

Detailed Explanation

Detailed Explanation

1. CREATE TABLE — Full Syntax

A CREATE TABLE statement has three parts:

1. Column definitions (name + type + per-column constraints)
2. Table-level constraints (composite PRIMARY KEY, FOREIGN KEY, multi-column UNIQUE)
3. Table options (ENGINE, CHARSET, COMMENT)

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(30) NOT NULL,
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    manager_id INT,
    hire_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

IF NOT EXISTS makes the statement idempotent:

CREATE TABLE IF NOT EXISTS employees (...);

If the table already exists, CREATE becomes a no-op (with a warning) instead of an error. Always use this in seed scripts.

2. ALTER TABLE — The Swiss Army Knife

Add a Column

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
-- Or specify where
ALTER TABLE employees ADD COLUMN phone VARCHAR(15) AFTER name;
ALTER TABLE employees ADD COLUMN phone VARCHAR(15) FIRST;

Drop a Column

ALTER TABLE employees DROP COLUMN phone;

Warning: the column and all its data are gone. No undo without backup.

Modify a Column (Type or Default, Keep Name)

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);
ALTER TABLE employees MODIFY COLUMN name VARCHAR(100) NOT NULL;

MODIFY is a MySQL-ism. The portable alternative is ALTER COLUMN ... TYPE ... in standard SQL.

Rename a Column (MySQL 8.0+)

ALTER TABLE employees RENAME COLUMN department TO dept;

-- Older MySQL (5.7) requires CHANGE with the new type repeated:
ALTER TABLE employees CHANGE COLUMN department dept VARCHAR(30) NOT NULL;

Rename the Table

ALTER TABLE employees RENAME TO staff;

-- Or the dedicated command:
RENAME TABLE employees TO staff;

Add a Constraint

ALTER TABLE employees
    ADD CONSTRAINT uq_email UNIQUE (email);

ALTER TABLE employees
    ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id);

ALTER TABLE employees
    ADD CONSTRAINT chk_salary CHECK (salary > 0);

Drop a Constraint

ALTER TABLE employees DROP FOREIGN KEY fk_manager;
ALTER TABLE employees DROP INDEX uq_email;         -- UNIQUE is an index
ALTER TABLE employees DROP CHECK chk_salary;
ALTER TABLE employees DROP PRIMARY KEY;

Multiple Changes in One Statement

ALTER TABLE employees
    ADD COLUMN joining_bonus DECIMAL(10,2) DEFAULT 0,
    DROP COLUMN phone,
    MODIFY COLUMN name VARCHAR(80) NOT NULL;

Running multiple changes together is faster than running them one at a time (fewer table rebuilds).

3. DROP TABLE vs TRUNCATE TABLE vs DELETE FROM

This comparison is the single most-asked DDL interview question.

Mental model:

• DELETE is a surgeon — precise, transactional, slow. Use when you want to remove specific rows.
• TRUNCATE is a bulldozer — wipes everything fast, resets counters. Use when you want to empty a whole table quickly.
• DROP is dynamite — removes the entire table. Use when the table should not exist anymore.

4. CREATE TABLE AS SELECT (CTAS)

-- Copy both structure AND data
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- Copy only matching rows
CREATE TABLE high_earners AS
SELECT * FROM employees WHERE salary > 80000;

CTAS is handy for quick backups or subset copies. Caveat: indexes, constraints, and AUTO_INCREMENT are NOT copied by default — only columns and data. To duplicate the full schema including indexes, use CREATE TABLE new_tbl LIKE old_tbl; followed by an INSERT.

5. TEMPORARY TABLE

CREATE TEMPORARY TABLE session_stats (
    user_id INT,
    clicks INT
);

A temporary table is visible only to the session that created it and is automatically dropped when the session disconnects. Perfect for intermediate results in long multi-step calculations or reports.

6. Defensive DDL: IF EXISTS / IF NOT EXISTS

DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees (...);
DROP DATABASE IF EXISTS test_db;
CREATE DATABASE IF NOT EXISTS test_db;

Use these in every seed script. They turn "Table already exists" errors into harmless no-ops. This is standard practice in production migration systems.

Code Examples

CREATE TABLE IF NOT EXISTS employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(30) NOT NULL,
    salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    hire_date DATE NOT NULL DEFAULT (CURRENT_DATE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO employees (name, email, department, salary) VALUES
    ('Kavya Nair',  'kavya@corp.in',  'HR',      60000.00),
    ('Arjun Mehta', 'arjun@corp.in',  'IT',      85000.00),
    ('Sneha Patel', 'sneha@corp.in',  'Finance', 72000.00),
    ('Rahul Joshi', 'rahul@corp.in',  'IT',      95000.00);

SELECT * FROM employees;

-- Add a phone column after name
ALTER TABLE employees ADD COLUMN phone VARCHAR(15) AFTER name;

-- Later, widen the name column and make it required
ALTER TABLE employees MODIFY COLUMN name VARCHAR(80) NOT NULL;

-- Rename 'department' to 'dept' (MySQL 8.0+)
ALTER TABLE employees RENAME COLUMN department TO dept;

-- Drop the phone column we added earlier
ALTER TABLE employees DROP COLUMN phone;

DESCRIBE employees;

-- Setup: fresh employees with 4 rows (see earlier INSERT)
SELECT COUNT(*) FROM employees;  -- 4

-- DELETE: remove specific rows with WHERE
DELETE FROM employees WHERE dept = 'IT';
SELECT COUNT(*) FROM employees;  -- 2 (Arjun and Rahul gone)

-- TRUNCATE: wipe everything, reset AUTO_INCREMENT
TRUNCATE TABLE employees;
SELECT COUNT(*) FROM employees;  -- 0

-- Reinsert one row to see AUTO_INCREMENT reset
INSERT INTO employees (name, email, dept, salary)
VALUES ('Test', 'test@corp.in', 'HR', 50000);
SELECT emp_id FROM employees;  -- 1 (reset to 1, not 5)

-- DROP: remove the table entirely
DROP TABLE employees;
SELECT * FROM employees;  -- ERROR: Table doesn't exist

-- Assume employees has been re-created with the original 4 rows.
DROP TABLE IF EXISTS employees_backup;

-- Quick full backup
CREATE TABLE employees_backup AS
SELECT * FROM employees;

-- Partial backup — just IT employees
DROP TABLE IF EXISTS employees_it;
CREATE TABLE employees_it AS
SELECT emp_id, name, salary
FROM employees
WHERE dept = 'IT';

SELECT * FROM employees_it;

-- Start with a bare table (no constraints yet).
CREATE TABLE IF NOT EXISTS departments (
    dept_id INT,
    dept_name VARCHAR(50),
    budget DECIMAL(12,2)
);

-- Add a PRIMARY KEY
ALTER TABLE departments
    ADD CONSTRAINT pk_dept PRIMARY KEY (dept_id);

-- Add a UNIQUE
ALTER TABLE departments
    ADD CONSTRAINT uq_dept_name UNIQUE (dept_name);

-- Add a CHECK
ALTER TABLE departments
    ADD CONSTRAINT chk_budget CHECK (budget > 0);

DESCRIBE departments;

-- Later, drop the CHECK we added
ALTER TABLE departments DROP CHECK chk_budget;

-- Create a temp table visible only to this session
CREATE TEMPORARY TABLE top_performers AS
SELECT emp_id, name, salary
FROM employees
WHERE salary > 80000;

-- Use it like any other table
SELECT * FROM top_performers;

SELECT COUNT(*) AS n FROM top_performers;

-- It disappears automatically when we DISCONNECT — no DROP needed.
-- If you log out and log back in: Table 'top_performers' doesn't exist.

Common Mistakes

-- Goal: wipe all rows for a fresh load
DROP TABLE employees;

-- If you want to keep the schema and wipe rows:
TRUNCATE TABLE employees;

-- If you want to wipe specific rows:
DELETE FROM employees WHERE dept = 'IT';

-- MySQL is forgiving and often accepts this, but it's ambiguous:
ALTER TABLE employees ADD phone VARCHAR(15);

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees MODIFY COLUMN name VARCHAR(80);

CREATE TABLE backup AS SELECT * FROM employees;
-- Assuming backup has the same PRIMARY KEY and AUTO_INCREMENT as employees

-- If you need structure too:
CREATE TABLE backup LIKE employees;   -- copies schema (constraints, indexes)
INSERT INTO backup SELECT * FROM employees;  -- then copy data

DROP TABLE employees;
-- But orders has FK to employees.

-- Option 1: drop dependent tables first
DROP TABLE orders;
DROP TABLE employees;

-- Option 2: remove the FK first
ALTER TABLE orders DROP FOREIGN KEY fk_emp;
DROP TABLE employees;

-- Option 3 (nuclear): temporarily disable FK checks
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE employees;
SET FOREIGN_KEY_CHECKS = 1;

Summary