CODEWITHSUNDEEP
phplaravelforeign-keyslaravel-9
Mohsen Gorzin
Mohsen Gorzin

Reputation: 186

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

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');

Upvotes: 3

Views: 3297

Answers (2)

user109764
user109764

Reputation: 654

In my case the problem was in the different datatypes of the referenced table key and the key reference. For instance, integer(unsigned) vs bigInteger(unsigned).

Upvotes: 1

Amin
Amin

Reputation: 138

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

Upvotes: 2

Related Questions