Question 240967 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Mysql foreign key referencing same table error General error: 1822 Failed to add the foreign key constraint. Missing index for constraint

I have a table
mytable

-- auto-generated definition
create table mytable
(
    id           varchar(255)                                                                                                                                  not null,
    name       varchar(255)                                                                                                                                  not null,
    parent_id    varchar(255)                                                                                                                                  null
)
    collate = utf8mb4_unicode_ci;

I want to make parent_id a foreign key to mytable.id

create index mytable_parent_id_index
    on mytable (parent_id);
alter table `mytable` add constraint `mytable_parent_id_foreign` foreign key (`parent_id`) references `mytable` (`id`);

the above is failing.

I am actually using Laravel migrations and do not write the mysql myself.

The code that gets executed is:

<?php declare(strict_types = 1);

use AppSupportMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateMyTableTable extends Migration
{
    private const TABLE = 'mytable';

    public function up(): void
    {
        Schema::create(self::TABLE, function (Blueprint $table): void {
            $table->string('id')->primary();
            $table->string('parent_id')->nullable()->index();
            $table->foreign('parent_id')->references('id')->on('places');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists(self::TABLE);
    }
}

You can see that I use ->index() on the parent_id field as well.

My question is:
How to have this working so that parent_id references as foreign key id on the same table?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I found the issue.

    The foreign key cannot be added without the table being created first.

    Needed to run a second separate script

    
        Schema::table(self::TABLE, function (Blueprint $table): void {
            $table->foreign('parent_id')->references('id')->on('mytable');
        });

  2. 0

    The parent_id has to be a integer. I recommend using a bigInteger, this gives you a larger range.

    <?php declare(strict_types = 1);

use AppSupportMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateMyTableTable extends Migration
{
    private const TABLE = 'mytable';

    public function up(): void
    {
        Schema::create(self::TABLE, function (Blueprint $table): void {
            $table->id();
            $table->bigInteger('parent_id')->unsigned()->nullable()->index();
            $table->foreign('parent_id')->references('id')->on('places');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists(self::TABLE);
    }
}
    Login or Signup to reply.
  3. 0

    The error message you’re seeing, "Missing index for constraint," is indicating that you need to add an index to the column you’re referencing in the foreign key constraint.

    Here’s how you can modify your migration to fix the issue:

    class CreateMyTableTable extends Migration
{
    private const TABLE = 'mytable';

    public function up(): void
    {
        Schema::create(self::TABLE, function (Blueprint $table): void {
            $table->string('id')->primary();
            $table->string('parent_id')->nullable(); // Remove the index here
        });

        Schema::table(self::TABLE, function (Blueprint $table): void {
            $table->index('parent_id'); // Add the index after creating the table
            $table->foreign('parent_id')->references('id')->on(self::TABLE);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists(self::TABLE);
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search