SQLite - Drop a Table

• Delete Data
• Drop a Database

To drop a table in SQLite, use the DROP TABLE statement.

Running this statement removes the table from the database. It is completely removed from the database schema and the disk file. Therefore the table can not be recovered. All indices and triggers associated with the table are also deleted.

A Basic DROP TABLE Statement

First, let's review the tables in our database:

sqlite> .tables
Albums   Albums1  Albums2  Artists

We have a couple of spare tables from our previous exercises, so let's drop one.

The following code deletes the Albums1 table:

After running that code, we can review our tables again:

sqlite> .tables
Albums   Albums2  Artists

The table has been removed.

Check if the Table Exists

You can use the IF EXISTS clause to check if the table exists before SQLite tries to drop it.

Doing this prevents any errors that would normally be generated if the table doesn't exist.

First, let's try to drop a non-existent table without using the IF EXISTS clause. Let's try to drop the one we just dropped:

This results in the following:

sqlite> DROP TABLE Album1;
Error: no such table: Album1

OK, so let's try it again, but this time with the IF EXISTS clause:

Result:

sqlite> DROP TABLE IF EXISTS Album1;
sqlite> 

So nothing happened. Importantly, no error was thrown.

Drop a Table with Primary Keys

So what happens if we try to drop a table that contains primary keys, and there are foreign keys referencing them?

Let's try it:

Result:

sqlite> DROP TABLE Artists;
Error: FOREIGN KEY constraint failed

As expected, it failed. So if you need to drop a table, you will need to drop any associated tables first.

• Delete Data
• Drop a Database