SQL As Understood By SQLite

ATTACH DATABASE

attach-stmt:

The ATTACH DATABASE statement adds another database file to the current database connection. If the filename contains punctuation characters it must be quoted. The database-names 'main' and 'temp' refer to the main database and the database used for temporary tables. These cannot be detached. Attached databases are removed using the DETACH statement.

You cannot create a new table with the same name as a table in an attached database, but you can attach a database which contains tables whose names are duplicates of tables in the main database. It is also permissible to attach the same database file multiple times.

Tables in an attached database can be referred to using the syntax database-name.table-name. If an attached table doesn't have a duplicate table name in the main database, it does not require a database-name prefix. When a database is attached, all of its tables which don't have duplicate names become the default table of that name. Any tables of that name attached afterwards require the database prefix. If the default table of a given name is detached, then the last table of that name attached becomes the new default.

Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:". If the main database is ":memory:" then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.

There is a compile-time limit of SQLITE_MAX_ATTACHED attached database files.