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