是否可以在 Laravel 的不同数据库中引用外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28950070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to reference a Foreign Key in a different database in Laravel?
提问by cchapman
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_dbthat contains a userstable, which I would like to use for the source of a few foreign keys in my own laravel_userstable in the laraveldatabase.
我正在尝试在 Laravel 4.2 中为该模型创建模型和迁移。我所有的 Laravel 应用程序都使用相同的 MySQL 数据库，名为laravel. 但是，我们还有另一个数据库（在同一台服务器上），称为main_db包含一个users表，我想将其用作数据库中我自己laravel_users表中的一些外键的来源laravel。
According to the Laravel documentation, I would designate a foreign key with this code:
根据 Laravel 文档，我会用以下代码指定一个外键：
$table->foreign('user_id')->references('id')->on('users');
But I believe this assumes that the 'users'table exists within the same database.
但我相信这假设该'users'表存在于同一个数据库中。
Is it possible to do trans-database foreign keys in Laravel? Would I have to first create a model that uses the userstable from main_db? And is it possible to set up two different database connections in the app/config/database.php?
是否可以在 Laravel 中执行跨数据库外键？我是否必须首先创建一个使用users表的模型main_db？是否可以在app/config/database.php? 中设置两个不同的数据库连接？
回答by lukasgeiter
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.
跨数据库外键实际上与 Laravel 没有太大关系。真正的问题是数据库是否支持它。MySQL（至少对于 InnoDB）确实支持跨多个数据库的外键约束。您只需要使用点表示法指定数据库：db.table。
Regarding the Laravel schema builder, this should work:
关于 Laravel 架构构建器，这应该有效：
$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).
如果出现错误，请检查列类型是否相同。（您不能将 varchar 引用到 int，反之亦然，键的类型必须相同）。
回答by travisneids
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
配置/数据库.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'));
});
回答by Iman Sedighi
Answer of @lukasgeiterworked for me with following two changes:
@lukasgeiter 的回答对我有用，有以下两个变化：
1- I changed both database engines to InnoDB.
1- 我将两个数据库引擎都更改为InnoDB。
2- I had to set the foreign key to be an Unsigned integerin the migration file as the reference was an unsigned integer and was the primary key.
2- 我必须在迁移文件中将外键设置为无符号整数，因为引用是一个无符号整数并且是主键。
$table->integer('user_id')->unsigned()->change();
