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.
ALTER TABLE customers ADD COLUMN phone TEXT; ALTER TABLE customers RENAME COLUMN phone TO mobile; ALTER TABLE customers DROP COLUMN mobile;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 ... |
-- 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 COLUMNhas limits in SQLite. It fails for a primary key, aUNIQUEcolumn, an indexed column, a column used in aCHECK, foreign key, view or trigger.- Added columns in SQLite cannot be
PRIMARY KEYorUNIQUE, and aNOT NULLcolumn needs a non-NULLdefault. - 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 ... SELECTthe rows, drop the old table, rename the new one. Do it in a transaction. - Back up first. Dropped columns cannot be restored.
Related
CREATE TABLE,DROP TABLE,RENAME, generated columns,IF EXISTS- Lessons: ALTER and DROP, Create table