How to Delete a Column in SQLite Database

When adding new columns, default values are provided. When deleting existing columns, all the values are sometimes deleted and even backed up depending on the use case.

This article will talk about how to remove a column from an SQLite database table with the help of some relevant examples.

Drop a Column From an SQLite Database Table

We can drop a column from an SQLite database table in two ways.

Drop a Column Using ALTER TABLE Statement

SQL offers an ALTER TABLE statement that can be used to add, delete, and modify existing columns in an existing database table. The following are the two syntaxes at our disposal we can use to drop a column.

Syntax:

ALTER TABLE <table_name> DROP COLUMN <column_name>;
ALTER TABLE <table_name> DROP <column_name>;

The above SQL statements drops column column_name from the existing table table_name. The DROP keyword indicates that the column will be dropped upon execution.

Refer to the following SQL script for a demo.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    age INT NOT NULL,
    gender TEXT NOT NULL
);

SELECT "Before Removal";
SELECT "--------------";

SELECT name FROM PRAGMA_TABLE_INFO("students"); -- Display all names columns

ALTER TABLE students DROP COLUMN last_name; -- Removing [last_name] column

SELECT "";
SELECT "After Removal";
SELECT "-------------";

SELECT name FROM PRAGMA_TABLE_INFO("students"); -- Display all names columns

Output:

Before Removal
--------------
id
first_name
last_name
age
gender

After Removal
-------------
id
first_name
age
gender

The SQL script above creates a new table, students, in the selected database. Next, it prints all the column names of the newly created table, and then using the ALTER TABLE statement, it removes the last_Name column.

Lastly, it again prints all the column names. From the above output, we see that the last_name column no longer exists in the table, and the ALTER TABLE statement successfully removed it.

Drop a Column by Creating a New Table

This approach is extremely naïve and takes more time than the previous approach. The idea is to create a new table from scratch with all the new columns (columns to be deleted will not be a part of this table).

Next, copy all the data from the old table to this new table as needed. Lastly, drop or delete the old table and use the new table as needed.

Refer to the following SQL script for an example.

-- creating the old table
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    age INT NOT NULL
);

-- inserting some data into the old table
INSERT INTO students VALUES (1, "Stefan", "Salvatore", 13);
INSERT INTO students VALUES (2, "Damon", "Salvatore", 14);
INSERT INTO students VALUES (3, "Elena", "Gilbert", 12);
INSERT INTO students VALUES (4, "Caroline", "Forbes", 12);
INSERT INTO students VALUES (5, "Bonnie", "Bennett", 13);

-- printing old table
SELECT "Students";
SELECT "--------";
SELECT * FROM students;
SELECT "";

-- creating new table
CREATE TABLE new_students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

-- inserting data from an old table into the new table
INSERT INTO new_students SELECT id, first_name, age FROM students;

-- printing new table
SELECT "New Students";
SELECT "--------";
SELECT * FROM new_students;
SELECT "";

-- dropping or deleting the old table
DROP TABLE students;

-- renaming the new table to the old table's name
ALTER TABLE new_students RENAME TO students;

-- printing new table
SELECT "Students";
SELECT "--------";
SELECT * FROM students;
SELECT "";

Output:

Students
--------
1|Stefan|Salvatore|13
2|Damon|Salvatore|14
3|Elena|Gilbert|12
4|Caroline|Forbes|12
5|Bonnie|Bennett|13

New Students
--------
1|Stefan|13
2|Damon|14
3|Elena|12
4|Caroline|12
5|Bonnie|13

Students
--------
1|Stefan|13
2|Damon|14
3|Elena|12
4|Caroline|12
5|Bonnie|13

The SQL script above first creates a table, students; it tries to mimic the old table. Next, it inserts some rows into the newly created table using the INSERT INTO statement and then prints the table content.

Now, a new table is created, namely, new_students; it mimics the new table. Note that the schema of the new_students table is different from the students table; it does not have the last_name column.

Next, using the INSERT INTO statement, all the rows from the students table are inserted into new_students.

Note that except the last_name, everything else is inserted. Additionally, the order of columns in the SELECT statement matters while inserting data from one table to another, and after insertion, the new table is printed.

Now that we don’t need the old table, the students table is deleted using the DROP TABLE statement, and the new_students table is renamed students using the ALTER TABLE statement. Lastly, the new table is printed using the new name, students.