SQLite ATTACH, DETACH database

ATTACH DATABASE

The ATTACH DATABASE statement allows you to attach multiple database files to the current database connection. When a database is attached, all of its contents are accessible in the global scope of the current database file.

The name that occurs after the AS keyword is the name of the database used internally by SQLite. The database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment, otherwise, you will get a warning message. The main and temp databases cannot be attached or detached.

Tables in an attached database can be used like database-name.table-name. When the table name is unique across all attached databases as well as the main and temp databases, then the database-name prefix is not required but when two or more tables in different databases have the same name , then it is necessary to specify the desired table using the database-name prefix.

More databases can be attached to your current session with the ATTACH DATABASE statement. This adds a connection to another database without replacing your currently selected database.

Here is the syntax:

ATTACH [DATABASE] filename AS database_name;

The filename for the database to be attached is the value of the expression, occurs before the AS keyword. The filename refers to the path and name of the SQLite database file, and database_name refers to the logical name with which to reference that database and its objects.

Example:

Sample Output:

D:\sqlite>sqlite3 medical.db
sqlite> .database
seq  name             file
---  ---------------  ------------------------
0    main             D:\sqlite\medical.db

Here in the above example shows that the main database is appearing and another database temp is hidden.

Let's attached two database hr and medical.db with three aliases, that are hrdb, medical and medical1

ATTACHhr AS hrdb;
ATTACH DATABASE 'medical.db' as 'medical';
ATTACH DATABASE 'medical.db' as 'medical1';

Now see the status of the current connection.

Sample Output:

sqlite> .database
seq  name             file
---  ---------------  ------------------------
0    main             D:\sqlite\medical.db
2    hrdb             D:\sqlite\hr
3    medical          D:\sqlite\medical.db
4    medical1         D:\sqlite\medical.db

In the above example we have attach three databses, one is hr and another two are medical.db in different aliases. The alias for hr database is hrdb and for medical.db are medical and medical1.If the database you specified, already not exists, then a database in the name will be created.

Now we are going to attach the database main and temp and look the result below.

Sample Output:

sqlite> attach main as main;
Error: database main is already in use
sqlite> attach temp as temp;
Error: database temp is already in use

DETACH DATABASE

SQLite DETACH DATABASE statement allows to detach and dissociate a named database from a database connection which was attached using ATTACH statement. If two or more database files in the same name have been attached with multiple aliases, then DETACH command will disconnect only the name given and rest of the attachment will still remain same. The database main or temp cannot be detached.

To detach an attached database, the syntax is simply

Sample Output:

DETACH DATABASE 'Alias-Name';

Example:

The following databases have already be attached.

Sample Output:

seq  name             file
---  ---------------  ------------------------------
0    main             D:\sqlite\medical.db
2    hrdb             D:\sqlite\hr
3    medical          D:\sqlite\medical.db
4    medical1         D:\sqlite\medical.db

Now if we want to detach the hr database, the following command can be used.

Sample Output:

sqlite> detach DATABASE hrdb;
sqlite> .database
seq  name             file
---  ---------------  ------------------------
0    main             D:\sqlite\medical.db
2    medical          D:\sqlite\medical.db
3    medical1         D:\sqlite\medical.db

Now let’s try to detach medical1 from medical.db as follows

sqlite> DETACH DATABASE 'medical1';

Now, if you want to check current attachment, you will find that medical.db is still connected with ‘medical’ and ‘main’.

Sample Output:

sqlite> .database
seq  name             file
---  ---------------  -----------------------------------
0    main             D:\sqlite\medical.db
3    medical          D:\sqlite\medical.db

Previous: DOT(.) Commands
Next: Data Types