SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint laravel 9

Question

Trying to assign foreign key but when you run migrate, I get this this error, I do not understand what the problem is.

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table categories add constraint categories_parent_key_foreign foreign key (parent_key) references categories (key) on delete cascade)

$table->bigIncrements('id');
$table->string('key', 64)->unique();
$table->string('parent_key', 64)->nullable()->index();
$table->string('title', 256)->index()->unique();
$table->foreign('parent_key')->references('key')
                ->on((new Category())->getConnection()->getDatabaseName() . '.' . Category::TABLE)
                ->onDelete('cascade');

Answer 1

I had the same problem. The problem arises when a model has a relationship with itself (self-relation). To solve this problem, first, the migration file must be created and then the foreign key must be assigned in another migration file. You must remove the foreign key assignment from the migration file and create the new migration file after that, then add relations statements to assign a foreign key. (the order of the migration files is important).

create_category_table

public function up(): void
{
    $table->bigIncrements('id');
    $table->string('key', 64)->unique();
    $table->string('parent_key', 64)->nullable()->index();
    $table->string('title', 256)->index()->unique();

}

create_category_relation_table

public function up(): void
{
    $table->foreign('parent_key')->references('key')
            ->on((new Category())->getConnection()->getDatabaseName() . '.' . Category::TABLE)
            ->onDelete('cascade');
}

And then php artisan migration