Tutorials SQL ALTER TABLE
Ch 10 / 69
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above
Chapter 10 Beginner 6 min read DDL

SQL ALTER TABLE

Database schemas are never truly finished. As requirements evolve, you'll constantly need to add new columns, rename existing ones, change data types, or remove columns that are no longer needed — all without deleting any existing data. The ALTER TABLE statement is the DDL command for all of this. In this chapter you'll master every ALTER TABLE operation with real-world examples and learn how syntax differs across MySQL, PostgreSQL, and SQL Server.

What you'll learn
What ALTER TABLE does and when to use it
ADD COLUMN — adding new columns to existing tables
MODIFY / ALTER COLUMN — changing data types and constraints
RENAME COLUMN — renaming columns safely
DROP COLUMN — permanently removing columns
Renaming entire tables with RENAME TABLE
Adding and dropping constraints and indexes
Cross-database syntax: MySQL, PostgreSQL, SQL Server
Risks, best practices, and production safety tips

What is ALTER TABLE?

ALTER TABLE is the SQL DDL command that modifies the structure of an existing table — without touching the data inside it. It is how you evolve your database schema as your application grows and requirements change.

Think of it as a renovation tool: CREATE TABLE builds the room, ALTER TABLE renovates it — knocking out walls (dropping columns), adding rooms (adding columns), and remodelling (changing types). The existing furniture (data) stays in place.

Add Column
ADD COLUMN
Insert one or more new columns into an existing table. New rows get the DEFAULT value or NULL.
Modify Column
MODIFY / ALTER COLUMN
Change a column's data type, size, NULL-ability, or default value.
Rename Column
RENAME COLUMN
Give a column a new name without losing any data. Updates all indexes automatically.
Drop Column
DROP COLUMN
Permanently remove a column and all its data from the table. Cannot be undone.
Rename Table
RENAME TO
Give the entire table a new name. FK references, views, and stored procedures may need updating.
Add/Drop Constraints
ADD / DROP CONSTRAINT
Add or remove PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, or DEFAULT constraints after creation.

ALTER TABLE — General Syntax

Every ALTER TABLE statement follows the same pattern: the table name, followed by one or more clauses separated by commas. Multiple changes can be made in a single statement in MySQL (which rebuilds the table once, not multiple times).

ALTER TABLE table_name
    clause_1,                   -- e.g. ADD COLUMN …
    clause_2,                   -- e.g. MODIFY COLUMN …
    clause_3;                   -- e.g. DROP COLUMN …
Blue = required keywords Purple = your table name Orange = ALTER TABLE clause
Pro Tip
In MySQL, combining multiple changes in one ALTER TABLE statement is much faster than running separate statements, because MySQL rebuilds the table only once. On large tables, each separate ALTER TABLE can take minutes — combine them when possible.

ADD COLUMN — Adding New Columns

The ADD COLUMN clause inserts a new column into an existing table. The new column is added with NULL in all existing rows (or the DEFAULT value if specified). In MySQL you can control the position of the new column using AFTER or FIRST.

ADD COLUMN — Before & After
⬤ BEFORE — students table
student_id name email
1 Priya priya@mail.com
2 Rahul rahul@mail.com
→
✓ AFTER — phone & city added
student_id name email phone city
1 Priya priya@mail.com NULL Vadodara
2 Rahul rahul@mail.com NULL Surat
SQL — ADD COLUMN variations (MySQL)
-- Add a single column (appended at end)
ALTER TABLE students
    ADD COLUMN phone VARCHAR(20);

-- Add a column with constraints and a DEFAULT
ALTER TABLE students
    ADD COLUMN status ENUM('active','inactive')
                NOT NULL DEFAULT 'active';

-- Add AFTER a specific column (MySQL only)
ALTER TABLE students
    ADD COLUMN city VARCHAR(80)
    AFTER email;

-- Add as the FIRST column (MySQL only)
ALTER TABLE students
    ADD COLUMN prefix CHAR(4)
    FIRST;

-- Add MULTIPLE columns in ONE statement (efficient)
ALTER TABLE students
    ADD COLUMN phone     VARCHAR(20),
    ADD COLUMN city      VARCHAR(80) AFTER email,
    ADD COLUMN is_active BOOLEAN     NOT NULL DEFAULT TRUE;
ADD COLUMN col type
Adds the column at the end of the table by default. Existing rows get NULL (or DEFAULT if supplied).
AFTER col_name
MySQL-only. Inserts the new column immediately after the named column. Useful for keeping related columns together visually.
NOT NULL DEFAULT 'x'
If the column is NOT NULL, you MUST also provide a DEFAULT — otherwise MySQL will reject the ALTER (existing rows would have NULL with no allowed NULL).
Advertisement
📢Ad space · 728 × 90

MODIFY / ALTER COLUMN — Changing Column Definition

You can change a column's data type, size, NULL-ability, or default value using MODIFY COLUMN (MySQL) or ALTER COLUMN (PostgreSQL / SQL Server). Be careful: changing a type can truncate existing data if the new type has a smaller capacity.

SQL — MODIFY COLUMN (MySQL) vs ALTER COLUMN (PostgreSQL/SQL Server)
═══ MySQL / MariaDB ════════════════════════════════════════════

-- Change data type (widen VARCHAR: safe)
ALTER TABLE students
    MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Add NOT NULL (all rows must already have values)
ALTER TABLE students
    MODIFY COLUMN email VARCHAR(150) NOT NULL UNIQUE;

-- Change column type and add DEFAULT
ALTER TABLE orders
    MODIFY COLUMN amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00;

-- CHANGE (rename + modify in one step — MySQL only)
ALTER TABLE students
    CHANGE old_col_name new_col_name VARCHAR(100) NOT NULL;

═══ PostgreSQL ═════════════════════════════════════════════════

ALTER TABLE students
    ALTER COLUMN name TYPE VARCHAR(200);

ALTER TABLE students
    ALTER COLUMN email SET NOT NULL;

ALTER TABLE orders
    ALTER COLUMN amount SET DEFAULT 0.00;

═══ SQL Server ═════════════════════════════════════════════════

ALTER TABLE students
    ALTER COLUMN name NVARCHAR(200) NOT NULL;
Data Truncation Risk
Narrowing a column (e.g. VARCHAR(200) → VARCHAR(50)) will silently truncate any existing values longer than 50 characters. Always check your data first with SELECT MAX(LENGTH(col)) FROM table; before narrowing.

RENAME COLUMN — Renaming Columns

Renaming a column is a common schema evolution task — perhaps your naming convention changed, or a column's purpose was clarified. Modern SQL databases support this with RENAME COLUMN, though older MySQL uses CHANGE.

SQL — Rename column across databases
═══ MySQL 8.0+ / MariaDB 10.5+ / PostgreSQL ═════════════════════

ALTER TABLE students
    RENAME COLUMN fname TO first_name;

═══ MySQL < 8.0 (CHANGE — must repeat the full column definition)

ALTER TABLE students
    CHANGE fname first_name VARCHAR(100) NOT NULL;

═══ SQL Server (stored procedure) ══════════════════════════════

EXEC sp_rename 'students.fname', 'first_name', 'COLUMN';

═══ SQLite (no direct rename — must recreate the table) ════════

-- SQLite 3.25.0+ supports RENAME COLUMN
ALTER TABLE students RENAME COLUMN fname TO first_name;
After Renaming
After renaming a column, you must update any views, stored procedures, triggers, and application queries that reference the old column name — the database does not auto-update these.

DROP COLUMN — Removing Columns

DROP COLUMN permanently removes a column and all its data from every row. There is no undo — once dropped, the data is gone unless you have a backup. Always back up before dropping columns in production.

SQL — DROP COLUMN
-- Drop a single column
ALTER TABLE students
    DROP COLUMN prefix;

-- Drop multiple columns in one statement (MySQL)
ALTER TABLE students
    DROP COLUMN middle_name,
    DROP COLUMN old_address;

-- PostgreSQL: same syntax
ALTER TABLE students
    DROP COLUMN IF EXISTS prefix;  -- IF EXISTS prevents error

-- SQL Server: same syntax as MySQL
ALTER TABLE students
    DROP COLUMN old_address;
Data is gone forever
Dropping a column deletes all values in that column across every row in the table. Without a backup, recovery is impossible.
Drop FK column last
If the column is referenced by a FOREIGN KEY, you must DROP FOREIGN KEY first, then drop the column — otherwise MySQL will refuse the operation.
Cannot drop PK directly
You must first drop the PRIMARY KEY constraint, then drop the column. Or use a two-step ALTER: remove PK, then drop column.
Views / queries break
Any view, stored procedure, or application query that references the dropped column will immediately fail with an error.

Renaming a Table

Sometimes the table name itself needs to change — perhaps you're refactoring a schema or correcting a naming convention. Renaming a table is a metadata-only operation; no data is moved.

SQL — Rename table across databases
═══ MySQL ═══════════════════════════════════════════════════════
-- Option 1: RENAME TABLE (preferred in MySQL)
RENAME TABLE old_name TO new_name;

-- Option 2: ALTER TABLE … RENAME TO
ALTER TABLE old_name RENAME TO new_name;

-- Rename multiple tables atomically (RENAME TABLE only)
RENAME TABLE
    students   TO learners,
    teachers   TO instructors,
    courses    TO programmes;

═══ PostgreSQL ═════════════════════════════════════════════════
ALTER TABLE old_name RENAME TO new_name;

═══ SQL Server ═════════════════════════════════════════════════
EXEC sp_rename 'old_name', 'new_name';
Update References After Renaming
After renaming a table, manually update all FOREIGN KEY references in other tables, views, stored procedures, and application connection strings / ORM models. The database does not auto-update these for you.

Adding & Dropping Constraints and Indexes

All constraint types can be added to or removed from existing tables using ALTER TABLE. This is commonly needed when you want to add data validation rules to an existing schema, or remove a constraint that's no longer needed.

SQL — Add and drop all constraint types
══ ADD CONSTRAINTS ══════════════════════════════════════════════

-- Add PRIMARY KEY
ALTER TABLE students
    ADD CONSTRAINT pk_students PRIMARY KEY (student_id);

-- Add FOREIGN KEY
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE;

-- Add UNIQUE
ALTER TABLE students
    ADD CONSTRAINT uq_student_email UNIQUE (email);

-- Add CHECK
ALTER TABLE students
    ADD CONSTRAINT chk_age CHECK (age BETWEEN 5 AND 100);

-- Add INDEX (for query performance)
ALTER TABLE students
    ADD INDEX idx_city (city);

══ DROP CONSTRAINTS ══════════════════════════════════════════════

-- Drop PRIMARY KEY
ALTER TABLE students DROP PRIMARY KEY;

-- Drop FOREIGN KEY (MySQL — must use FK constraint name)
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- Drop UNIQUE (MySQL treats UNIQUE as an INDEX)
ALTER TABLE students DROP INDEX uq_student_email;

-- Drop CHECK (MySQL 8.0+)
ALTER TABLE students DROP CHECK chk_age;

-- Drop any named constraint (PostgreSQL / SQL Server)
ALTER TABLE students DROP CONSTRAINT chk_age;

-- Drop INDEX
ALTER TABLE students DROP INDEX idx_city;
-- or: DROP INDEX idx_city ON students;
Advertisement
📢Ad space · 728 × 90

Real-World Example — Evolving a Schema

Here is a realistic migration script showing how the students table evolves over time as requirements change — exactly how you'd manage a schema in a real project:

SQL — Complete schema evolution script (MySQL)
-- ─────────────────────────────────────────────────────────────
-- v1.0 → v1.1  Sprint 2: Add phone and city fields
-- ─────────────────────────────────────────────────────────────
ALTER TABLE students
    ADD COLUMN phone VARCHAR(20) AFTER email,
    ADD COLUMN city  VARCHAR(80) NOT NULL DEFAULT 'Unknown' AFTER phone;

-- ─────────────────────────────────────────────────────────────
-- v1.1 → v1.2  Sprint 4: Widen name field + add status
-- ─────────────────────────────────────────────────────────────
ALTER TABLE students
    MODIFY COLUMN name   VARCHAR(150) NOT NULL,
    ADD COLUMN    status ENUM('active','inactive','graduated')
                         NOT NULL DEFAULT 'active';

-- ─────────────────────────────────────────────────────────────
-- v1.2 → v1.3  Sprint 6: Rename 'fname' to 'first_name'
-- ─────────────────────────────────────────────────────────────
ALTER TABLE students
    RENAME COLUMN fname TO first_name;

-- ─────────────────────────────────────────────────────────────
-- v1.3 → v1.4  Sprint 8: Add index for performance + drop legacy col
-- ─────────────────────────────────────────────────────────────
ALTER TABLE students
    ADD INDEX   idx_city     (city),
    ADD INDEX   idx_status   (status),
    DROP COLUMN legacy_field;

-- ─────────────────────────────────────────────────────────────
-- v1.4 → v1.5  Sprint 10: Add UNIQUE on email + CHECK on age
-- ─────────────────────────────────────────────────────────────
ALTER TABLE students
    ADD CONSTRAINT uq_email UNIQUE       (email),
    ADD CONSTRAINT chk_age  CHECK        (age BETWEEN 5 AND 100),
    ADD COLUMN    age       TINYINT UNSIGNED AFTER first_name;
1
Back up the table before any destructive change
Run CREATE TABLE students_backup AS SELECT * FROM students; before any DROP COLUMN or type narrowing operation.
2
Test on a dev/staging copy first
Run the ALTER TABLE statement on a copy of the production database before running on the real server. Check that all queries and app functions still work correctly.
3
Combine changes in one ALTER TABLE per sprint
In MySQL, one ALTER TABLE with multiple clauses rebuilds the table once. Separate statements rebuild it multiple times — far slower on large tables.
4
Run during low-traffic windows on production
ALTER TABLE can lock the table for the duration on older MySQL versions. Use pt-online-schema-change or MySQL 8+ ALGORITHM=INPLACE for zero-downtime migrations.

ALTER TABLE Syntax Across SQL Databases

The core operations are universal, but the exact keywords differ between database systems. Here's the quick-reference cheat sheet:

Operation MySQL / MariaDB PostgreSQL SQL Server
Add column ADD COLUMN col type ADD COLUMN col type ADD col type
Modify type MODIFY COLUMN col newtype ALTER COLUMN col TYPE newtype ALTER COLUMN col newtype
Rename column RENAME COLUMN old TO new (8.0+) or CHANGE old new type RENAME COLUMN old TO new EXEC sp_rename 'tbl.old', 'new', 'COLUMN'
Drop column DROP COLUMN col DROP COLUMN col DROP COLUMN col
Set NOT NULL MODIFY COLUMN col type NOT NULL ALTER COLUMN col SET NOT NULL ALTER COLUMN col type NOT NULL
Set DEFAULT MODIFY COLUMN col type DEFAULT val ALTER COLUMN col SET DEFAULT val ADD DEFAULT val FOR col
Rename table RENAME TABLE old TO new ALTER TABLE old RENAME TO new EXEC sp_rename 'old', 'new'
Add index ADD INDEX name (col) CREATE INDEX name ON tbl(col) CREATE INDEX name ON tbl(col)
Drop FK DROP FOREIGN KEY name DROP CONSTRAINT name DROP CONSTRAINT name
📊
Free Resource
Download the SQL Cheat Sheet
All ALTER TABLE operations across MySQL, PostgreSQL, and SQL Server — one printable page.
Get Sheet

Best Practices & Production Safety

ALTER TABLE is one of the most dangerous DDL operations in production — it can lock tables, corrupt data if done carelessly, and cause application downtime. Follow these practices to stay safe:

Always Back Up First
Create a backup table or a database dump before any destructive change. CREATE TABLE t_bak AS SELECT * FROM t;
Test on Staging First
Run every ALTER TABLE on a staging copy of your production database before touching production. Verify all queries still work.
Combine Changes
In MySQL, one ALTER TABLE with multiple clauses is faster than many separate statements — the table is rebuilt only once.
Low-Traffic Windows
Schedule ALTER TABLE on large tables for off-peak hours. It can lock the table for seconds to minutes depending on table size.
Check Data Before Narrowing
Before reducing a VARCHAR size, run SELECT MAX(LENGTH(col)) FROM tbl; to confirm no values will be truncated.
Version-Control Your Schema
Use migration tools (Flyway, Liquibase, or versioned SQL files) to track every ALTER TABLE as a versioned, repeatable migration script.

Try It Yourself

The in-browser runner is loaded with the students table after several ALTER TABLE operations — showing the result of added, modified, and renamed columns. Explore the data and schema with SELECT queries.

SQL Query Runner In-Browser
Tables: students (id, first_name, email, phone, city, status, age, created_at)  ·  Try:
Results will appear here after you run a query
Quick Check — Chapter 10

You need to add a new NOT NULL column called country to an existing table that already has 10,000 rows. What must you also include?

Chapter Summary
  • ALTER TABLE modifies the structure of an existing table without deleting any data. It is the DDL command for all schema evolution needs.
  • ADD COLUMN inserts a new column. If NOT NULL, you must also specify a DEFAULT so existing rows receive a valid value. Use AFTER col or FIRST in MySQL to control position.
  • MODIFY COLUMN (MySQL) / ALTER COLUMN (PostgreSQL, SQL Server) changes a column's data type, size, NULL-ability, or default. Never narrow a type without checking existing data length first.
  • RENAME COLUMN old TO new renames a column (MySQL 8.0+, PostgreSQL). Use CHANGE in older MySQL (must repeat the full column definition). SQL Server uses sp_rename.
  • DROP COLUMN permanently removes a column and all its data with no undo. Drop FK references first, and always back up before dropping in production.
  • RENAME TABLE old TO new (MySQL) or ALTER TABLE old RENAME TO new (PostgreSQL) renames a table. After renaming, manually update all FK references, views, and application code.
  • Constraints and indexes can be added post-creation: ADD CONSTRAINT name TYPE (col). They can be removed with DROP FOREIGN KEY, DROP INDEX, DROP CHECK, or DROP CONSTRAINT depending on the database.
  • In MySQL, combining multiple operations in a single ALTER TABLE rebuilds the table only once — much faster than running separate ALTER TABLE statements on large tables.
  • Always back up, test on staging, combine changes, and schedule large ALTER TABLE runs during low-traffic periods. Use tools like pt-online-schema-change for zero-downtime migrations on production.
Advertisement
📢Ad space · 728 × 90

Rate Us on Google

4.8 / 5
Enjoying this tutorial?
Your 5-star review helps other learners discover our free tutorials and encourages us to keep creating quality content.
80+ reviews
Write a Google Review
Join WhatsApp Channel