SQLite - Drop a Table
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.
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:
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:
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.
SQLite actually tries to delete the data before it drops a table. It is this delete action that causes any foreign key constraint checks to take place.
SQLite also drops any indices or triggers associated with the table before it performs the delete (and subsequent drop) operation.