How to Use Alter Command in SQLite
SQLite is a popular and widely used relational database management system that is known for its simplicity and portability. It provides a wide range of commands to manage databases, tables, and their properties. One of the most useful commands in SQLite is the ALTER command, which allows users to modify the structure of an existing table. In this article, we will discuss how to use the ALTER command in SQLite to add, modify, or delete columns from a table.
To begin with, it is essential to understand that the ALTER command is only available in SQLite if the SQLite version is 3.8.3 or higher. Before using the ALTER command, make sure you are using a compatible version of SQLite.
To add a new column to an existing table, you can use the following syntax:
“`sql
ALTER TABLE table_name ADD COLUMN column_name column_type;
“`
Here, `table_name` is the name of the table to which you want to add the column, `column_name` is the name of the new column, and `column_type` is the data type of the new column. For example, to add a new column named “age” of type INTEGER to a table named “students”, you can use the following query:
“`sql
ALTER TABLE students ADD COLUMN age INTEGER;
“`
If you want to modify the data type or properties of an existing column, you can use the ALTER TABLE command with the `ALTER COLUMN` clause. The syntax is as follows:
“`sql
ALTER TABLE table_name ALTER COLUMN column_name new_column_type;
“`
Here, `new_column_type` is the new data type or properties you want to assign to the column. For instance, if you want to change the data type of the “age” column in the “students” table to TEXT, you can use the following query:
“`sql
ALTER TABLE students ALTER COLUMN age TEXT;
“`
To delete a column from an existing table, you can use the ALTER TABLE command with the `DROP COLUMN` clause. The syntax is as follows:
“`sql
ALTER TABLE table_name DROP COLUMN column_name;
“`
Here, `column_name` is the name of the column you want to delete. For example, to remove the “age” column from the “students” table, you can use the following query:
“`sql
ALTER TABLE students DROP COLUMN age;
“`
In summary, the ALTER command in SQLite is a powerful tool that allows you to modify the structure of your tables by adding, modifying, or deleting columns. By following the syntax and examples provided in this article, you can effectively use the ALTER command to manage your SQLite database.
