How to delete or add column in SQLITE?

In SQLite, the native SQL syntax does not support directly deleting or adding columns. However, we can achieve this functionality indirectly through certain methods. Below are the steps and examples for adding and deleting columns:

Adding Columns

Adding columns in SQLite is relatively straightforward; you can directly use the ALTER TABLE command to add columns. Here is its basic syntax:

sql
ALTER TABLE table_name ADD COLUMN column_name column_type;

Example:

Suppose we have a table named students and we want to add a new column email to store students' email addresses with the data type TEXT. We can use the following command:

sql
ALTER TABLE students ADD COLUMN email TEXT;

This command adds a new column email to the students table with the data type TEXT.

Deleting Columns

Deleting columns in SQLite is slightly more complex because the ALTER TABLE command does not natively support deleting columns directly. We need to follow these steps:

1. Create a new table: The new table includes only the columns you wish to retain from the original table.
2. Copy data: Transfer the data from the original table to the new table, including only the columns you wish to retain.
3. Drop the original table: Delete the original table.
4. Rename the new table: Rename the new table to the original table's name.

Example:

Suppose we have a table named students and we want to delete the age column. We can follow these steps:

sql
BEGIN TRANSACTION;
-- Create a new table with all needed columns except `age`
CREATE TABLE new_students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

-- Insert data from original table into new table, excluding `age`
INSERT INTO new_students (id, name, email)
SELECT id, name, email FROM students;

-- Drop the original table
DROP TABLE students;

-- Rename the new table to the original name
ALTER TABLE new_students RENAME TO students;

COMMIT;

By following these steps, we successfully remove the age column from the students table.

This is how to add and delete columns in SQLite. Although deleting columns involves more steps, following this process typically allows safe modification of the table structure.