Migration with multiple banks in Laravel

Asked

Viewed 246 times

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) is INT UNSIGNED?

  • It would help a lot if you show us SQL/Blueprint that defines the relationship between the databases.

  • Gee! I checked the table id column type banco2.clients and was mediumint. I changed the Migration code to $table->mediumInteger('customer_id')->unsigned(); and it worked! Thanks so much @juniorb2ss and @Frenetic for the help.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.