SQLite - ALTER TABLE Command

Advertisements


The SQLite ALTER TABLE command modifies an existing table without performing a full dump and reload of the data. You can rename a table using ALTER TABLE statement and additional columns can be added in an existing table using ALTER TABLE statement.

There is no other operation supported by ALTER TABLE command in SQLite except renaming a table and adding a column in existing table.

Syntax:

The basic syntax of ALTER TABLE to RENAME an existing table is as follows:

ALTER TABLE database_name.table_name RENAME TO new_table_name;

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

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

Example:

Consider 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 this table using ALTER TABLE statement as follows:

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

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

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

Now, COMPANY table is changed and following would be output from SELECT statement:

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

It should be noted that newly added column is filled with NULL values.



Advertisements
Advertisements