Overview
The SQLite ALTER TABLE statement is used to modify an existing database table.
We can use this statement to add, modify or drop columns.
SQLite ALTER TABLE Statement
The general syntax of the statement is as follows:
ALTER TABLE table_name
<modification>;
The modification is the action or the alteration on the table. For example, to add a column to the table, we can use the following statement:
ALTER TABLE table_name
ADD COLUMN column_def….;
Example
Let’s alter the emp table to add a new column. We will use the ALTER TABLE statement with ADD COLUMN clause to add the new column.
sqlite> ALTER TABLE emp
…> ADD COLUMN bonus INTEGER;
Notice the Runtime error when we try to add the column with the NOT NULL constraint. This is because the new column values for the rows will have NULL as the default value.
We can use the DEFAULT column constraint to add a default value other than NULL. For example, to add a new column bonus with a default value of 0, we can use the following statement:
sqlite> ALTER TABLE emp
…> ADD COLUMN bonus INTEGER DEFAULT 0;
—
SQLite Tutorials
SQLite tutorials on this website:
https://www.testingdocs.com/sqlite-database-tutorials/
For more information on SQLite, visit the official website: