SQLite Drop Table

In this article we will show how to drop a table in SQLite database using the DROP TABLE statement.
The DROP TABLE statement followed by a table name drops the table from the SQLite database.

Using the optional IF EXISTS clause avoids a possible error that would result if the table we want to delete does not exist.

When the table is removed, all objects associated with a table such as indexes, triggers are deleted. After deletion, the table cannot be recovered.

When the DROP TABLE statement is executed and foreign key constraints are enabled, an implicit DELETE FROM command is automatically executed before removing the table from the database schema.

Syntax

DROP TABLE [IF EXISTS] [schema_name].table_name;

Example

In the following example, we will create two main tables linked by a foreign key: the students table and the student_address table.

The student_address table will have two associated objects, a trigger (address_log_ins) and an index (address_id_idx).

At the end we will remove the student_address table using the DROP TABLE statement.
When the student_address table is deleted from the SQLite database, the objects associated with it will also be deleted.

CREATE TABLE IF NOT EXISTS students(
   student_id INTEGER PRIMARY KEY,
   first_name TEXT,
   last_name TEXT,
   birthday DATE,
   address_id INTEGER,
   FOREIGN KEY (address_id) 
   REFERENCES student_address (address_id)
);

CREATE TABLE IF NOT EXISTS student_address(
   address_id INTEGER PRIMARY KEY,
   city TEXT,
   country TEXT
);

CREATE TABLE student_address_log(
   address_id INTEGER,
   city TEXT,
   country TEXT,
   log_date DATE,
   log_message TEXT
);

CREATE TRIGGER IF NOT EXISTS address_log_ins
BEFORE INSERT ON student_address
FOR EACH ROW
BEGIN
INSERT INTO student_address_log(address_id,city,country,log_date,log_message) 
VALUES (NEW.address_id,NEW.city,NEW.country,DATETIME(),'insert');
END;

CREATE INDEX address_id_idx ON student_address(address_id);

INSERT INTO student_address(address_id,city,country) 
VALUES (100,'San Antonio','US');
INSERT INTO student_address(address_id,city,country) 
VALUES (101,'San Jose','US');
INSERT INTO student_address(address_id,city,country) 
VALUES (102,'Philadelphia','US');

INSERT INTO students(student_id,first_name,last_name,birthday,address_id) 
VALUES (1,'Paul','Smith','2002-10-14',100);
INSERT INTO students(student_id,first_name,last_name,birthday,address_id) 
VALUES (2,'Charlotte','Jones','2003-05-18',101);
INSERT INTO students(student_id,first_name,last_name,birthday,address_id) 
VALUES (3,'Theodore','Hernandez','2003-08-12',102);

Before executing a drop statement, we need to make sure we have foreign key constraints enabled. For this, execute the command below.

PRAGMA foreign_keys = ON;

The next step is to remove the table from the database following the syntax described at the beginning of the article.

DROP TABLE IF EXISTS student_address;

The SQLite database returned the error message:

FOREIGN KEY constraint failed

To remove the student_address table, execute the following instructions:

Disable foreign key constraints.

PRAGMA foreign_keys = OFF;

Drop the student_address table.

DROP TABLE IF EXISTS student_address;

Enable foreign key constraints.

PRAGMA foreign_keys = ON;