Is it possible to reference a Foreign Key in a different database in Laravel?

Question

I'm trying to create a model and a migration for that model in Laravel 4.2. All of my Laravel apps use the same MySQL database called laravel. However, we also have another database (on the same server), called main_db that contains a users table, which I would like to use for the source of a few foreign keys in my own laravel_users table in the laravel database.

According to the Laravel documentation, I would designate a foreign key with this code:

$table->foreign('user_id')->references('id')->on('users');

But I believe this assumes that the 'users' table exists within the same database.

Is it possible to do trans-database foreign keys in Laravel? Would I have to first create a model that uses the users table from main_db? And is it possible to set up two different database connections in the app/config/database.php?

Answer 1

Cross database foreign keys hasn't much to do with Laravel actually. The real question is if the database does support it. And MySQL (at least with InnoDB) does support foreign key constraints accross multiple databases. You just have to specify the database with the dot notation: db.table.

Regarding the Laravel schema builder, this should work:

$table->foreign('user_id')->references('id')->on('main_db.users');
//                                                ^^^^^^^

If you get an error, check if the column types are the same. (You can't reference varchar to int or vice versa, keys have to be of same type).

Answer 2

For future reference if you ever change the name of the database this will not work. It is best to grab the name of the database by the database definition.

config/database.php

'auth_database' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

Then in your migration file:

Schema::create('role_user', function (Blueprint $table) {
    $db = DB::connection('auth_database')->getDatabaseName();

    $table->integer('role_id')->unsigned();
    $table->foreign('role_id')->references('id')->on('roles');
    $table->integer('user_id')->unsigned();
    $table->foreign('user_id')->references('id')->on(new Expression($db . '.users'));
});