Latest web development tutorials

SQLite Alter command

SQLite through theALTER TABLE command does not perform a complete dump and reload the data to modify an existing table.You can use the ALTER TABLE statement to rename a table, use the ALTER TABLE statement can also add extra columns in an existing table.

In SQLite, in addition to rename the table and add columns to existing tables, ALTER TABLE command does not support other operations.

grammar

To rename an existing tableALTER TABLE basic syntax is as follows:

ALTER TABLE database_name.table_name RENAME TO new_table_name;

To add a new column in an existing tableALTER TABLE basic syntax is as follows:

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

Examples

Suppose we have the following records in the COMPANY table: 

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 use the ALTER TABLE statement to rename the table as follows: 

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

The above statement will rename COMPANY SQLite table 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 has been changed, the output of the SELECT statement 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 column is a NULL value to fill.