Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL ALTER TABLE statement

Reference for SQL ALTER TABLE: add, rename and drop columns, rename tables, what SQLite supports and how PostgreSQL, MySQL, SQL Server and Oracle differ.

ALTER TABLE changes the structure of an existing table without losing its rows. Typical jobs are adding a column, renaming a column or table, dropping a column and changing constraints or types.

How much it can do depends on the database. SQLite supports a deliberately small set: rename the table, rename a column, add a column and drop a column. Everything else (changing a type, adding a constraint to an existing column) is done by building a new table and copying the rows.

SQL
ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers DROP COLUMN mobile;

Try it

SQL Add, rename and drop a column ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Rename a table, and a limit (fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME TO new_table_name;
Part Description
ADD COLUMN Appends a column. Existing rows get its default value, or NULL when there is none.
RENAME COLUMN Renames a column. Views, triggers and indexes that use it are updated in modern SQLite.
DROP COLUMN Removes a column and its data.
RENAME TO Renames the table.

Dialect differences

Task SQLite PostgreSQL MySQL SQL Server Oracle
Add column ADD COLUMN ADD COLUMN ADD COLUMN ADD (no COLUMN) ADD
Rename column RENAME COLUMN a TO b same same (8.0+) sp_rename RENAME COLUMN a TO b
Drop column DROP COLUMN DROP COLUMN DROP COLUMN DROP COLUMN DROP COLUMN
Change type not supported ALTER COLUMN c TYPE t MODIFY COLUMN c t ALTER COLUMN c t MODIFY c t
Add constraint not supported ADD CONSTRAINT ... ADD CONSTRAINT ... ADD CONSTRAINT ... ADD CONSTRAINT ...
SQL
-- PostgreSQL
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10, 2);
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price >= 0);

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

-- SQL Server
ALTER TABLE products ADD discontinued BIT NOT NULL DEFAULT 0;
EXEC sp_rename 'products.discontinued', 'is_discontinued', 'COLUMN';

Usage notes

  • DROP COLUMN has limits in SQLite. It fails for a primary key, a UNIQUE column, an indexed column, a column used in a CHECK, foreign key, view or trigger.
  • Added columns in SQLite cannot be PRIMARY KEY or UNIQUE, and a NOT NULL column needs a non-NULL default.
  • Locking and speed. On big tables, some changes rewrite every row and block writers. PostgreSQL and MySQL are fast for adding a column with a default in recent versions, but changing a type may rewrite the table.
  • Rebuild pattern for unsupported changes: create the new table, INSERT ... SELECT the rows, drop the old table, rename the new one. Do it in a transaction.
  • Back up first. Dropped columns cannot be restored.