Migration for self referencing many to many relation failing in Laravel with "Foreign key constraint is incorrectly formed"

Question

I try to implement a self referencing many to many relationship. So a product can have one or more alternative products. When I run the migration I get this error:

SQLSTATE[HY000]: General error: 1005 Can't create table xxx.productalternatives (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table productalternatives add constraint productalternatives_original_product_id_foreign foreign key (original_product_id) references products (id))

And thats how my migration looks like:

Schema::create('productalternatives', function (Blueprint $table) {
  $table->unsignedBigInteger('original_product_id'); 
  $table->unsignedBigInteger('alternate_product_id'); $table->timestamps(); 
  $table->foreign('original_product_id')->references('id')->on('products'); 
  $table->foreign('alternate_product_id')->references('id')->on('products');
});

Any ideas whats the reason for that?

Answer 1

Based on my experience, when you are working with a table that needs a self-referencing foreign key, you have to first create the table and then (in a new "query") add the foreign key:

Schema::create('productalternatives', function (Blueprint $table) {
    $table->unsignedBigInteger('original_product_id'); 
    $table->unsignedBigInteger('alternate_product_id');
    $table->timestamps(); 
});

Schema::table('productalternatives', function (Blueprint $table) {
    $table->foreign('original_product_id')->references('id')->on('products'); 
    $table->foreign('alternate_product_id')->references('id')->on('products');
});