In SQLite, you can drop a table with the DROP TABLE statement.

You can optionally add the IF EXISTS clause to suppress any errors that might occur if the table doesn’t exist.

Also, if the table is referenced by a foreign key, there are a few things to be aware of.

Example

Here’s an example to demonstrate the simplest way of dropping a table in SQLite:

DROP TABLE t1;

This drops the table called t1.

You can optionally prefix the table name with the schema name.

Using the IF EXISTS Clause

You can use the IF EXISTS clause to suppress any errors that might occur in the event that the table doesn’t exist.

DROP TABLE IF EXISTS t2;

If we remove the IF EXISTS clause, and run that again, we get an error.

Example:

DROP TABLE t2;

Result:

Error: no such table: t2 

Foreign Key & View Dependencies

SQLite doesn’t support the CASCADE and RESTRICT keywords, which are included in the SQL standard, and are supported by some other RDBMSs (such as PostgreSQL). These keywords are designed to specify what to do when the target table has dependencies (such as a view or foreign key that references the table).

Given SQLite doesn’t support these keywords, here’s how SQLite deals with views and foreign keys when you try to drop a table.

SQLite ignores any dependent views. In other words, it goes ahead and drops the table, even if there’s a view that references it.

As for foreign keys…

First, foreign keys are disabled by default in SQLite. So unless you enable them, any foreign keys that reference the target table will have no impact on the dropping of the target table. In other words, the table will be dropped.

If foreign keys are enabled in your session, then they will only prevent the dropping of the table if there is data that will violate that foreign key. If your child table contains no data, then the parent table will be dropped without error. If the child table contains data (presumably including data that references the parent table’s primary key), then this will result in an error and the table won’t be dropped.

Like this:

PRAGMA foreign_keys = ON;
DROP TABLE t1;

Result:

Error: FOREIGN KEY constraint failed 

The reason it works like this is because SQLite performs an implicit DELETE FROM operation before dropping the table. If the DELETE FROM operation results in a foreign key violation, then we get the error. But if there is no data in the table, then any DELETE FROM operation will not result in a foreign key violation, and the table can be dropped.

In short, it’s not the DROP TABLE operation that causes any foreign key violations, it’s the implicit DELETE FROM operation.

When the Foreign Key uses ON DELETE CASCADE

However, if the foreign key constraint is defined with ON DELETE CASCADE, then the parent table will be dropped, and any rows that reference that table’s primary key column will be deleted in the child table.

Here’s an example.

Create tables and insert data:

CREATE TABLE t11 (
    c1 integer  PRIMARY KEY AUTOINCREMENT
    );
CREATE TABLE t12 (
    c1 integer  PRIMARY KEY AUTOINCREMENT, 
    c2 integer REFERENCES t11 (c1) ON DELETE CASCADE
    );
INSERT INTO t11 VALUES (1);
INSERT INTO t12 VALUES (1, 1);

Select data:

sqlite> SELECT * FROM t11;
c1
--
1 
sqlite> SELECT * FROM t12;
c1  c2
--  --
1   1 

Drop the parent table and review all tables:

sqlite> DROP TABLE t11;
sqlite> .tables
t12

We can see that t11 no longer exists, but t12 still exists.

Check the foreign key on t12:

sqlite> PRAGMA foreign_key_list(t12);
id  seq  table  from  to  on_update  on_delete  match
--  ---  -----  ----  --  ---------  ---------  -----
0   0    t11    c2    c1  NO ACTION  CASCADE    NONE 

Yes, that still exists, and we can confirm that it has ON DELETE CASCADE.

Select data from t12:

sqlite> SELECT * FROM t12;
sqlite> 

No rows are returned. This is because the ON DELETE CASCADE option on the foreign key ensured that the row was deleted when the parent table (t11) was dropped (or more precisely, when its data was deleted via the implicit DELETE FROM operation prior to being dropped).

Dropping a Foreign Key

SQLite doesn’t actually support the dropping of foreign keys. Normally in SQL you drop foreign keys with the ALTER TABLE statement, but SQLite’s ALTER TABLE implementation doesn’t allow for dropping constraints.

There is a way to deal with this situation though. See How to Drop a Foreign Key in SQLite for an example.