How to Drop a Column from the SQLite Table

The database is used to store the application data in a structured format. Many database tools exist to store the application data in tabular format. SQLite is one of them. It is a very lightweight database tool and is mainly used to implement the mobile applications. The SQL syntax for SQLite databases to do the different types of database operations is a little bit different than the other standard database tools such as MySQL, Oracle, SQL Server, etc. The sqlite3 is installed on the Linux operating system by default. So, you don’t need to install it. The method to drop one or more columns from the SQLite table is shown in this tutorial.

Prerequisite:

You have to complete the following tasks before practicing the SQLite statements of this tutorial:

A. Run the following command to create an SQLite file named “company.db” that contains the database tables:

sqlite3 company.db

B. Run the following SQLite command to check whether the database is created or not:

.databases

C. Run the following SQL statement to create a table named “products”. The table contains five fields and one primary key:

CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
TYPE TEXT NOT NULL,
brand TEXT NOT NULL,
price INETEGER NOT NULL);

D. Run the following SQL statement to create a table named “suppliers”. The table contains five fields, one primary key, and one foreign key. So, the “products” table and “suppliers” table are connected with the foreign key:

CREATE TABLE suppliers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
address TEXT NOT NULL,
brand TEXT NOT NULL,
product_id INETEGER NOT NULL,
FOREIGN KEY(product_id) REFERENCES products(id));

E. Run the necessary commands to format the output and check the structure of the “products” table with header and tabular form.

The following command displays the header of the output:

.header on

The following command displays the column-based output:

.mode column

The following command displays the structure of the “products” table in tabular form:

pragma table_info('products');

The structure of the “products” table is shown in the following image with the heading:

Syntax:

The syntax of the SQL command to drop the column of the table is given in the following. The ALTER TABLE statement is used to delete or add the column of the table.

ALTER TABLE TABLE_NAME DROP COLUMN column_name;

Different Examples to Drop a Column from SQLite Table

The three ways to drop the column from an SQLite table are shown in this part of the tutorial.

Example 1: Drop the Column from the Table

Run the following ALTER TABLE statement to remove the “brand” field from the “products” table:

ALTER TABLE products DROP COLUMN brand;

Run the following SQLite command to check whether the “brand” field is deleted or not from the “products” table:

pragma table_info('products');

The output shows that the “brand” field is removed from the “products” table.

Example 2: Drop the Column from the Table that is a Primary Key

The primary key is used in the table to identify each record separately. So, the primary can’t be dropped from the table and an error appears if the ALTER TABLE statement is used to remove the primary key field. Run the following ALTER TABLE statement to remove the “id” field from the “products” table which is the primary key of the table:

ALTER TABLE products DROP COLUMN id;

The following error appears if you want to drop the primary key of the table:

Example 3: Drop a Column from the Table that is a Foreign Key

Like the primary key, the foreign key of the table can’t be dropped until the relationship between the tables is removed. Run the following ALTER TABLE statement to remove the “product_id” field from the “suppliers” table which is foreign to the table:

ALTER TABLE suppliers DROP COLUMN product_id;

The following error appears if you want to drop the foreign key of the table:

Conclusion

Three different types of examples are shown in this tutorial to drop a column from an SQLite table where it is examined that the primary and foreign key fields of the table can’t be dropped.

How to Drop a Column from the SQLite Table

Prerequisite:

Syntax:

Different Examples to Drop a Column from SQLite Table

Conclusion

About the author

Fahmida Yesmin