How to Drop Table in SQLite. Explain with example.

SQLite Drop Table

The SQLite DROP TABLE statement allows you to remove or delete a table from the SQLite database. 

In SQLite once we drop table all the data it contains are permanently removed from the database. Any associated indexes and triggers are also removed. Views that might reference the table are not removed and Delete triggers will not be fired.

If there is any foreign key constraint enabled on table, then that will remove equivalently for each row in the table and any triggers associated with the table also will be dropped. 

While dropping the table if any constraint violation occurs then DROP TABLE command fails and commits the current transaction.

In default mode, if you fire DROP TABLE command then it will create free space in database by removing table and data but it will not reduce the size of database file. So to remove free space of database, SQLite uses VACUUM command. If AUTOVACUUM mode is enabled for a database, then space will be free automatically by DROP TABLE.

By using DROP table statement in SQLite we can remove virtual table also.

SQLite Drop Table Syntax

Following is the basic syntax of dropping table in SQLite.

DROP TABLE [ IF EXISTS ] table_name;

In above sqlite drop table syntax we have different properties those are

table_name: Name of the table to remove from database.

IF EXISTS: It is optional clause. If specified, the DROP TABLE statement will not raise an error if one of the tables does not exist.

Here note that If you use the SQLite DROP TABLE statement to drop a table that does not exist, the database will raise an error (unless you specify the IF EXISTS parameter in the DROP TABLE statement).

SQLite Drop Table Example

Following is the example of dropping one table.

DROP TABLE contacts;

The above SQLite DROP TABLE example will delete the table called contacts.