In this article you will learn how to modify the structure of a table.
SQLite supports a number of ways to modify the structure and contents of tables. The most common cases are those where you have to change the name of a table, add new columns or constraints to a table.
ALTER TABLE command is used to add, remove, or modify columns in an existing table.
ALTER TABLE command can also be used to add and drop various constraints on an existing table.
SQLite ALTER TABLE does not support complex changes in the structure or constraints of a table or its columns. To make complex changes you have to recreate the table, before the creation save the existing data in a temporary table, then delete the old table, then you can create the new table with the desired structure and finally you can copy back the data from the temporary table.
Below we will show the syntax of ALTER TABLE, and how it is used to modify the structure of a table.
1. Rename table name
ALTER TABLE table_name RENAME TO new_table_name;
2. Adding a column to a SQLite table
ALTER TABLE table_name ADD COLUMN column_name datatype;
3. Adding a NOT NULL constraint to a SQLite table column
ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL DEFAULT default_value;
4. Adding a CHECK constraint to a SQLite table column
ALTER TABLE test1 ADD COLUMN f TEXT CHECK (LENGTH (f) <= 50);
5. Dropping a SQLite table column
ALTER TABLE table_name DROP COLUMN column_name;
SQLite ALTER TABLE command is used to make changes to the existing database tables. It can be used to add new columns, remove existing columns. SQLite ALTER TABLE command is very versatile and it can be used to make a wide variety of changes to SQLite database tables.