SQLite: DETACH DATABASE Command

This SQLite post explains how to use the SQLite DETACH DATABASE command with syntax and examples.

Description

The SQLite DETACH DATABASE command is used to detach an attached database from your current database connection.

Syntax

The syntax for the DETACH DATABASE command in SQLite is:

DETACH [DATABASE] database_name;

Parameters or Arguments

DATABASE

Optional. The command can be run as either DETACH DATABASE or DETACH.

database_name

The logical name for the database that you wish to detach from the current database connection.

Note

• You can not detach the main or temp databases. The database name called main is reserved for the primary database and the database name called temp is reserved for the database that holds temporary tables.
• If you detach a file-based database, the database will detach and its contents will still exist in the database file on disk.
• If you detach an in-memory database, the database will detach but its contents will be lost.
• When the database connection is closed, all attached databases will be automatically be detached.
• You can not detach a database inside a transaction.

Example

Let's look at an example of how to detach an attached database in SQLite.

First, we'll use the .database command to show all the databases in our current connection:

sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------

0    main             /AODBA/test.sqlite
2    example          /AODBA/example.sqlite

As you can see, we have our primary database called main that can be found in the location /AODBA/test.sqlite as well as an attached database called example that is located at /AODBA/example.sqlite. Next, let's detach the example database with the following command:

sqlite> DETACH DATABASE example;

Now that we have detached the example database, let's re-run the .database command to show all of the databases in our current connection:

sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------

0    main             /AODBA/test.sqlite

Now you can see that the example database has been detached and only the main database is available in the current database connection.

To attach a database, use the ATTACH DATABASE command.