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 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.
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 …
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.
| student_id | name | |
|---|---|---|
| 1 | Priya | priya@mail.com |
| 2 | Rahul | rahul@mail.com |
| student_id | name | phone | city | |
|---|---|---|---|---|
| 1 | Priya | priya@mail.com | NULL | Vadodara |
| 2 | Rahul | rahul@mail.com | NULL | Surat |
-- 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;
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.
═══ 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;
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.
═══ 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;
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.
-- 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;
DROP FOREIGN KEY first, then drop the column —
otherwise MySQL will refuse the operation.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.
═══ 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';
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.
══ 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;
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:
-- ─────────────────────────────────────────────────────────────
-- 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;
CREATE TABLE students_backup AS SELECT * FROM students;
before any DROP COLUMN or type narrowing operation.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 |
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:
CREATE TABLE t_bak AS SELECT * FROM t;SELECT MAX(LENGTH(col)) FROM tbl; to confirm no values
will be truncated.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.
students (id, first_name, email, phone, city, status, age, created_at)
· Try:
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?
- 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 colorFIRSTin 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
CHANGEin older MySQL (must repeat the full column definition). SQL Server usessp_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 withDROP FOREIGN KEY,DROP INDEX,DROP CHECK, orDROP CONSTRAINTdepending 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.
Rate Us on Google
Join Our Learning Community
Connect with thousands of learners, get daily tips, ask questions and stay updated with new tutorials.