SQLite ALTER Command

SQLite'sALTER TABLEThe command does not modify existing tables by performing a complete dump and reload of data. You can use the ALTER TABLE statement to rename a table, and you can also use the ALTER TABLE statement to add extra columns to an existing table.

In SQLite, apart from renaming a table and adding columns to an existing table, the ALTER TABLE command does not support other operations.

Syntax

Used to rename an existing tableALTER TABLEThe basic syntax is as follows:

ALTER TABLE database_name.table_name RENAME TO new_table_name;

Used to add a new column to an existing tableALTER TABLEThe basic syntax is as follows:

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

Examples

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's try to rename the table using the ALTER TABLE statement, as follows:

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

The above SQLite statement will rename the COMPANY table to OLD_COMPANY. Now, let's try to add a new column to the OLD_COMPANY table, as follows:

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

Now, the COMPANY table has changed. Using the SELECT statement, the output 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 newly added column is filled with NULL values.

Other Extensions