ALTER TABLE

-- Adds a column.
ALTER TABLE table_name ADD COLUMN column_name data_type [constraint];

-- Drops a column.
ALTER TABLE table_name DROP COLUMN column_name;

-- Changes the definition of a column (MySQL).
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type [constraint];

-- Changes the column name and definition at the same time (MySQL).
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name data_type [constraint];

-- Renames the table.
ALTER TABLE table_name RENAME TO new_table_name;

-- Adds a phone number column to the members table.
ALTER TABLE members ADD COLUMN phone VARCHAR(20) AFTER email;

-- Expands the maximum length of the name column from 100 to 200 characters (MySQL).
ALTER TABLE members MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Renames a column from the old name to the new name (MySQL).
ALTER TABLE members CHANGE COLUMN registed_at registered_at DATETIME NOT NULL;

-- Drops a column that is no longer needed.
ALTER TABLE members DROP COLUMN old_column;

-- Renames the table.
ALTER TABLE users RENAME TO members;

-- Applies multiple changes in a single statement (MySQL).
ALTER TABLE products
    ADD COLUMN description TEXT AFTER product_name,
    MODIFY COLUMN price DECIMAL(12, 2) NOT NULL;

-- Example SHOW COLUMNS result after running: ALTER TABLE members ADD COLUMN phone VARCHAR(20) AFTER email;
-- +---------------+--------------+------+-----+-------------------+
-- | Field         | Type         | Null | Key | Default           |
-- +---------------+--------------+------+-----+-------------------+
-- | member_id     | int          | NO   | PRI | NULL              |
-- | name          | varchar(200) | NO   |     | NULL              |
-- | email         | varchar(255) | NO   | UNI | NULL              |
-- | phone         | varchar(20)  | YES  |     | NULL              |
-- | registered_at | datetime     | NO   |     | CURRENT_TIMESTAMP |
-- +---------------+--------------+------+-----+-------------------+

The basic syntax for adding and dropping columns is supported by all major databases.

-- Adding a column (common)
ALTER TABLE members ADD COLUMN phone VARCHAR(20);

-- Dropping a column (common)
ALTER TABLE members DROP COLUMN old_column;

The syntax for changing a column definition varies by database. MySQL uses MODIFY COLUMN, PostgreSQL uses ALTER COLUMN ... TYPE, and SQL Server uses ALTER COLUMN.

-- MySQL
ALTER TABLE members MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- PostgreSQL
ALTER TABLE members ALTER COLUMN name TYPE VARCHAR(200);

-- SQL Server
ALTER TABLE members ALTER COLUMN name NVARCHAR(200) NOT NULL;

The syntax for renaming a column also differs. MySQL uses CHANGE COLUMN, PostgreSQL uses RENAME COLUMN, and SQL Server uses the sp_rename procedure.

-- MySQL
ALTER TABLE members CHANGE COLUMN registed_at registered_at DATETIME NOT NULL;

-- PostgreSQL
ALTER TABLE members RENAME COLUMN registed_at TO registered_at;

-- SQL Server
EXEC sp_rename 'members.registed_at', 'registered_at', 'COLUMN';

Specifying an insertion position with ADD COLUMN ... AFTER existing_column is supported only in MySQL. In PostgreSQL, Oracle, and SQL Server, columns are always appended at the end. SQLite supports DROP COLUMN only as of version 3.35.0.

ALTER TABLE is used frequently on live databases. Adding or modifying columns on large tables can cause a table lock that blocks all access. In production environments, plan a maintenance window in advance, or consider using the Online DDL features supported by storage engines such as InnoDB.

DROP COLUMN permanently deletes both the column and its data. Because the risk of accidental data loss is high, it is strongly recommended to take a backup before executing this statement.

To create a new table, see CREATE TABLE. To add or drop indexes, see CREATE INDEX.