cchapman cchapman - 2 months ago 6
MySQL Question

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

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

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');
//                                                ^^^^^^^