I've created a basic database structured as follows.
vehicle_makes (id, name, slug)
vehicle_models (id, name, slug, make_id)
vehicle_trims (id, name, slug, model_id)
When attempting to create the vehicle_trims table via Laravel migrations, I get an error about duplicate foreign key constraint.
SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'id' (Connection: mysql, SQL: alter table `vehicle_trims` add constraint `id` foreign key (`model_id`) references `vehicle_models` (`id`) on delete cascade on update cascade)
After reading similar posts from other users here, I understand that the FK name has to be unique, but this error seems to be related to each table having a standard 'id' column.
Does that mean tables needs a unique ID such as make_id, model_id, etc. to use foreign keys, and then reference these with fk_make_id, fk_model_id and so on instead?
Migrations
vehicle_makes
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('vehicle_makes', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('slug');
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('vehicle_makes');
}
};
vehicle_models
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('vehicle_models', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('slug');
$table->foreignId('make_id')
->constrained(table: 'vehicle_makes', indexName: 'id')
->onUpdate('cascade')
->onDelete('cascade');
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('vehicle_models');
}
};
vehicle_trims
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('vehicle_trims', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('slug');
$table->year('year');
$table->foreignId('model_id')
->constrained(table: 'vehicle_models', indexName: 'id')
->onUpdate('cascade')
->onDelete('cascade');
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('vehicle_trims');
}
};
->constrained(table: 'vehicle_models', indexName: 'id')
indexName here is the name for the index, not the column, so it has to be unique e.g vehicle_trims_model_id, but you can omit it completely and Laravel will use a default convention for indexes naming.
If you had a differently named key in foreign table than 'id' you could use
$table->foreign('model_id')->references('not_conventional_id')->on('vehicle_models');
https://laravel.com/docs/11.x/migrations#foreign-key-constraints