SQLite Alter command
SQLite's ALTER TABLE command does not modify an existing table by performing a complete dump and reload of the data. You can use the ALTER TABLE statement to rename a table and to add additional columns to an existing table.
In SQLite, the ALTER TABLE command does not support other operations except renaming tables and adding columns to existing tables.
Syntax
The basic syntax of ALTER TABLE used to rename an existing table is as follows:
ALTER TABLE database_name.table_name RENAME TO new_table_name;
Used to rename an existing table The basic syntax of ALTER TABLE to add a new column to the table is as follows:
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
Example
Suppose 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 us try to rename the table using ALTER TABLE statement as shown below:
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
The above SQLite statement will rename the COMPANY table to OLD_COMPANY. Now, let us try to add a new column in the OLD_COMPANY table as shown below:
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
Now, the COMPANY table has been changed and 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
Please note that the new The added columns are filled with NULL values.
