I'm trying to create a migration that has a foreign key referencing the id of a table from another database, both MySQL.
Follow the code:
//config/database.php
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
'mysql2' => [
'driver' => 'mysql',
'host' => env('DB2_HOST', '127.0.0.1'),
'port' => env('DB2_PORT', '3306'),
'database' => env('DB2_DATABASE', 'forge'),
'username' => env('DB2_USERNAME', 'forge'),
'password' => env('DB2_PASSWORD', ''),
'unix_socket' => env('DB2_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
// migration
Schema::create('customer_partner', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
$table->integer('partner_id')->unsigned();
$table->foreign('partner_id')->references('id')->on('partners')->onDelete('cascade');
$table->integer('customer_id')->unsigned();
$table->foreign('customer_id')->references('id')->on(env('DB2_DATABASE').'.clients');
});
However when running the command php artisan migrate
the following error occurs:
[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table 'customer_partner' add constraint 'customer_partner_customer_id_foreign' foreign key ('customer_id') references 'banco2'.'clients' ('id'))
[PDOException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
Any idea what I might be doing wrong?