Laravel Migration: Second foreign key not created as secondary key in phpMyAdmin

85 Views Asked by At

Description:

I'm facing an issue with Laravel migrations when creating a pivot table. I have a migration that defines a pivot table with two foreign keys and a composite primary key. However, when I run the migration and check the table in phpMyAdmin, only one foreign key is created as a secondary key, while the other is not.

Code:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('endpoint_parameter', function (Blueprint $table) {

            // Columns creation:
            $table->unsignedBigInteger('id_parameter');
            $table->unsignedBigInteger('id_endpoint');

            // Set Primary Key
            $table->primary(['id_parameter', 'id_endpoint']);

            // Add columns 'created_at' and 'updated_at'
            $table->timestamps();

            // Set foreign key referring to endpoint table
            $table->foreign('id_endpoint', 'fk_endpoint')
                ->references('id_endpoint')->on('endpoint')
                ->onUpdate('cascade')->onDelete('cascade');

                // Set foreign key referring to parameter table 
            $table->foreign('id_parameter', 'fk_parameter')
            ->references('id_parameter')->on('parameter')
            ->onUpdate('cascade')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('endpoint_parameter');
    }
};

Expected behavior::

I expect that both foreign keys (id_parameter and id_endpoint) in the pivot table endpoint_parameter should be created as secondary keys in phpMyAdmin.

I also expect to retrieve BTREE key: kf_parameter on index list, but the latter is not detected

Actual behavior:

After running the migration, I checked the table in phpMyAdmin, and only one foreign key (id_endpoint) is created as a secondary key. The second foreign key (id_parameter) is not created as a secondary key.

Exported table as text:

-- Table structure for table `endpoint_parameter`
CREATE TABLE `endpoint_parameter` (
  `id_parameter` bigint(20) UNSIGNED NOT NULL,
  `id_endpoint` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Indexes for dumped tables

-- Indexes for table `endpoint_parameter`
ALTER TABLE `endpoint_parameter`
  ADD PRIMARY KEY (`id_parameter`,`id_endpoint`),
  ADD KEY `fk_endpoint` (`id_endpoint`);

-- Constraints for dumped tables

-- Constraints for table `endpoint_parameter`
ALTER TABLE `endpoint_parameter`
  ADD CONSTRAINT `fk_endpoint` FOREIGN KEY (`id_endpoint`) REFERENCES `endpoint` (`id_endpoint`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_parameter` FOREIGN KEY (`id_parameter`) REFERENCES `parameter` (`id_parameter`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Additional information:

Laravel version: "laravel/framework": "^9.19" phpMyAdmin version: 5.2.1 Database: MariaDB

I have already tried running the migration multiple times and clearing the cache, but the issue persists. It seems that adding the primary key declaration $table->primary(['id_parameter', 'id_endpoint']); does not create the composite primary key as expected. I tried removing the primary key from the code and it correctly associate both of the foreign keys as such, so I am sure both of them are correctly identified by the sistem (no errors on code concerning syntax)

Result of SQL command "SHOW INDEXES FROM endpoint_parameter":

  0  PRIMARY  1  id_parameter     A  0  NULL  NULL  BTREE
  0  PRIMARY  2  id_endpoint      A  0  NULL  NULL  BTREE
  1  fk_endpoint  1  id_endpoint  A  0  NULL  NULL  BTREE
0

There are 0 best solutions below