SQLite is a powerful database management system that is widely used for organizing, storing, and retrieving data. Data in SQLite is stored in tables, which can have any number of columns, each with its own data type. In some cases, you may need to add or delete a column in a table, which can be done easily using SQLite commands or tools.
We will look at how to delete or add a column in SQLite in this tutorial.
To understand how to add or delete a column, it is important to understand the basic structure of an SQLite table.
Structure of an SQLite Table
A table in SQLite is made up of rows and columns. Each column denotes a particular data field, and each row denotes a single record.
To add or delete a column in a table, you need to know the name of the table, the name of the column you want to add or delete, and the data type of the new column.
id INTEGER PRIMARY KEY,
employee_code TEXT UNIQUE,
name TEXT,
position TEXT
);
Data can be inserted into the table using:
VALUES ('E001', 'John Doe', 'Manager');
How to Add a Column in SQLite?
Adding a column to a table is a straightforward process in SQLite. An existing table can have a new column added to it using the ALTER TABLE command. The basic syntax of the ALTER TABLE command is as follows:
In this command, table_name is the name of the table you want to modify, new_column_name is the name of the new column you want to add, and data_type is the data type of the new column.
For example, to add a new column called email to a table employees with a data type of TEXT, you can use the following command:
After you execute this command, the new column email will be added to the employees table, and all new records added to the table will include an email field.
You can verify this using the PRAGMA Command.
How to Delete a Column in SQLite?
Deleting a column from a table is also possible using SQLite. The ALTER TABLE command can be used to remove a column from an existing table. The basic syntax of the command is as follows:
In this command, table_name is the name of the table you want to modify, and column_name is the name of the column you want to delete. For example, to delete the email column from the employees table, you would use the following command:
After you execute this command, the email column will be deleted from the employees table, and all new records added to the table will no longer include an email field.
You can verify this using the PRAGMA command too:
Conclusion
In SQLite, we can dynamically modify the structure of tables by adding or deleting columns. Such adjustment is possible using the ALTER TABLE statement’s ADD COLUMN and DROP COLUMN clauses. We looked at the syntax and application of these statements in this post and included code snippets to show how they were implemented.
