I understand that it is possible in MySQL, but I can not figure out how to reference a foreign key in one sqlite database to another SQLite database.

Below is an example that I attempted to replicate based on my online research, only to discover that it is for MySQL. I can not seem to find any questions about SQLite DB referencing another SQLite DB via Laravel.

Below snippets shows for example say we have 2 SQLite databases; sqlite-a and sqlite-b, where we want to reference a foreign key from sqlite-a to reference a key in sqlite-b.

Schema::connection('sqlite-a')->create('Changelog', function (Blueprint $table) {
    $db = DB::connection('sqlite-b')->getDatabaseName();

    $table->id();
    $table->dateTime('date');
                
    $table->bigInteger('user_id')->nullable()->unsigned();

    $table->foreign('user_id')->references('id')->on(new Expression($db . '.User'));

    $table->timestamps();
});

The error that I have encountered when I tried to do php artisan migrate is that there is a syntax error in the referencing.

SQLSTATE[HY000]: General error: 1 near "/": syntax error (SQL: create table "Changelog" ("id" integer not null primary key autoincrement, "date" datetime not null, "description" varchar not null, "user_id" integer, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references /path/to/sqlite/database/b.sqlite.User("id"))

I would love to hear from anyone who has dealt with this before or has a solution.

At this point, I believe it is not possible that two SQLitedatabases can reference each other. In MySQL, this is possible.

CodePudding user response：

When talking about foreign keys, SQLite's built-in foreign key constraints do not work across databases. But you can make cross database calls:

attach 'database1.db' as db1;
attach 'database2.db' as db2;

select
  *
from
  db1.SomeTable a
    inner join 
  db2.SomeTable b on b.SomeColumn = a.SomeColumn;