Can I refer to a foreign key in another database in Laravel?

Question

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

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 that it is assumed that the 'users' table exists within the same database.

Can I use Trans-database foreign keys in Laravel? Should I first create a model that uses the users table from main_db ? And is it possible to establish two different database connections in app/config/database.php ?

Answer 1

Cross-database foreign keys have nothing to do with Laravel. The real question is whether the database supports it. And MySQL (at least with InnoDB) supports foreign key restrictions across multiple databases. You just need to specify the database using dot notation: db.table .

As for the Laravel schema constructor, this should work:

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

Answer 2

In the future, if you ever change the database name, this will not work. It is best to get the database name by 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 the 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')); }); 

Answer 3

@ Lukasgeiter's answer worked for me with the following two changes:

1- I changed both database engines to InnoDB .

2- I had to set the foreign key in the Unsigned integer in the migration file, since the link was an unsigned integer and was the main key.

 $table->integer('user_id')->unsigned()->change();