May 16, 2021 SQLite
SQLite's ALTER TABLE command does not modify an existing table by performing a full dump and overloading the data. You can rename a table using the ALTER TABLE statement, and you can use the ALTER TABLE statement to add additional columns to an existing table.
In SQLite, the ALTER TABLE command does not support anything other than renaming tables and adding columns to existing tables.
The basic syntax for ALTER TABLE to rename an existing table is as follows:
ALTER TABLE database_name.table_name RENAME TO new_table_name;
The basic syntax for ALTER TABLE to add a new column to an existing table is as follows:
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
Let's say our COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
Now, let's try renaming the table with the ALTER TABLE statement, as follows:
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
The SQLite statement above renames the COMPANY table OLD_COMPANY. Now let's try adding OLD_COMPANY column to the table, as follows:
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
Now that the COMPANY table has changed, the output using the SELECT statement is as follows:
ID NAME AGE ADDRESS SALARY SEX ---------- ---------- ---------- ---------- ---------- --- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
Note that the newly added columns are populated with NULL values.