1
I am trying to create a Migration that has a foreign key referencing the id of a table from another database, both Mysql.
Follows 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');
});
But when executing 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?
banco2
.clients
(id
) isINT UNSIGNED
?– juniorb2ss
It would help a lot if you show us SQL/Blueprint that defines the relationship between the databases.
– FReNeTiC
Gee! I checked the table id column type
banco2.clients
and wasmediumint
. I changed the Migration code to$table->mediumInteger('customer_id')->unsigned();
and it worked! Thanks so much @juniorb2ss and @Frenetic for the help.– Aléx de Oliveira