Summary: in this tutorial, you will learn how to remove a table from the database using SQLite DROP TABLE statement.
Introduction to SQLite DROP TABLE statement
To remove a table in a database, you use SQLite DROP TABLE statement. The statement is simple as follows:
DROP TABLE [IF EXISTS] [schema_name.]table_name;
You specify the table name, which you want to remove, after the
DROP TABLE clause.
SQLite allows you to drop one table at a time. To remove multiple tables, you need to issue multiple
DROP TABLE statements.
If you remove a non-existent table, SQLite issues an error. If you use
IF EXISTS option, SQLite removes the table only if the table exists, otherwise, it just ignores the statement and does nothing.
If you want to remove a table in a specific database, you use the
DROP TABLE statement performs an implicit DELETE statement before deleting the table. However, the
DROP TABLE statement removes the triggers associated with the table before performing the implicit
DELETE statement, therefore, no triggers are fired.
If the foreign key constraints enabled and you perform the
DROP TABLE statement, before SQLite performs the implicit
DELETE statement, it invokes foreign key constraints check. If the foreign key constraint is violated, SQLite issues an error message and will not drop the table.
Notice that the
DROP TABLE statement removes the table from the database and the file on disk completely. You cannot undo or recover from this action. Therefore, you should perform the
DROP TABLE statement with extra caution.
SQLite DROP TABLE statement examples
For the demonstration purpose, we will create two tables:
addresses. Each person has one address. And one address can be shared by many people.
CREATE TABLE IF NOT EXISTS people (
person_id integer PRIMARY KEY,
FOREIGN KEY (address_id) REFERENCES addresses (address_id)
CREATE TABLE IF NOT EXISTS addresses (
address_id integer PRIMARY KEY,
We insert an address and a person into the
INSERT INTO addresses (
'North 1st Street',
'San Jose ',
INSERT INTO people (
('John', 'Doe', 1);
Now, we use the
DROP TABLE statement to remove the
DROP TABLE addresses;
SQLite issued an error message:
Because this action violates the foreign key constraint.
To remove the addresses table, you have to:
- First, disable foreign key constraints.
- Next, drop the
- Then, update the
- After that, enable foreign key constraints.
See the following statements:
PRAGMA foreign_keys = OFF;
DROP TABLE addresses;
SET address_id = NULL;
PRAGMA foreign_keys = ON;
The addresses table is removed and values of the
address_id column are updated to
In this tutorial, you have learned how to use SQLite
DROP TABLE statement to remove the table completely from a database.