SQL ALTER TABLE: Add, Rename, Change and Drop Columns

Requirements change. You will need to add a phone number column, rename a confusing column, or tighten a rule, on tables that already contain data. ALTER TABLE changes a table's structure without recreating it.

The example table#

customers

Add a column#

ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);

SELECT * FROM customers;

customer_id | name  | city    | phone
------------+-------+---------+------
1           | Asha  | Mumbai  | NULL
2           | Ravi  | Delhi   | NULL
3           | Meera | Pune    | NULL
4           | John  | Chennai | NULL

Existing rows get NULL in the new column, because nobody has supplied a value yet. (COLUMN is optional in most databases; SQL Server and Oracle write just ADD phone VARCHAR(20).)

Add a column with a default#

Give the column a DEFAULT and existing rows are filled in automatically:

ALTER TABLE customers
ADD COLUMN country VARCHAR(50) DEFAULT 'India';

SELECT name, country FROM customers;

name  | country
------+--------
Asha  | India
Ravi  | India
Meera | India
John  | India

Rename a column#

ALTER TABLE customers
RENAME COLUMN city TO home_city;

SELECT * FROM customers LIMIT 2;

customer_id | name | home_city
------------+------+----------
1           | Asha | Mumbai
2           | Ravi | Delhi

This syntax works in PostgreSQL, MySQL 8+, SQLite 3.25+ and Oracle. SQL Server uses a stored procedure: EXEC sp_rename 'customers.city', 'home_city', 'COLUMN';

Rename a table#

ALTER TABLE customers RENAME TO clients;

(SQL Server: EXEC sp_rename 'customers', 'clients'; MySQL also accepts RENAME TABLE customers TO clients;)

Change a column's data type#

The syntax differs most here:

-- PostgreSQL
ALTER TABLE customers ALTER COLUMN phone TYPE VARCHAR(30);

-- MySQL
ALTER TABLE customers MODIFY COLUMN phone VARCHAR(30);

-- SQL Server
ALTER TABLE customers ALTER COLUMN phone VARCHAR(30);

-- Oracle
ALTER TABLE customers MODIFY phone VARCHAR2(30);

Widening a type (20 to 30 characters) is safe. Narrowing it, or converting text to numbers, fails if any existing value does not fit. SQLite cannot change a column's type in place; you create a new table, copy the data, and swap them.

Drop a column#

ALTER TABLE customers
DROP COLUMN phone;

The column and all its data are deleted permanently. SQLite supports this since version 3.35.

Add and remove constraints#

You can add rules to existing tables. Name your constraints so they are easy to drop later:

ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE products
DROP CONSTRAINT chk_price_positive;

Adding a constraint fails if existing data already breaks it. In our sample data, order 104 points to customer 5, which does not exist, so the foreign key above would be rejected until that row is fixed. SQLite only supports adding constraints by rebuilding the table.

Changes on large tables#

On a table with millions of rows, some ALTER TABLE operations rewrite the entire table and can lock it while they run. Adding a nullable column is usually instant; changing a type is often not. Production teams schedule these changes carefully, often using "migration" tools that track every schema change in version control.

Common mistakes#

Practice#

ALTER TABLE customers
ADD COLUMN loyalty_points INTEGER NOT NULL DEFAULT 0;

SELECT name, loyalty_points FROM customers;

name  | loyalty_points
------+---------------
Asha  | 0
Ravi  | 0
Meera | 0
John  | 0

Key takeaway#