Laravel migration (errno: 150 "Foreign key constraint is incorrectly formed") - Laravel 8

Question

I have a products table and a product_exchanges table that has references products.id as a foreign. However, when I run the Laravel migration I got this error:

SQLSTATE[HY000]: General error: 1005 Can't create table `test`.`product_exchanges` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `product_exchanges` add constraint `product_exchanges_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete cascade on update cascade)

product table

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->text('name');   
    $table->boolean('active')->default(1);
    $table->timestamps();
});

products_exchanes table

Schema::create('product_exchanges', function (Blueprint $table) {
    $table->id();
    $table->dateTime('date');
    $table->double('amount')->unsigned();
    $table->integer('count_from')->unsigned();
    $table->integer('count_to')->unsigned();
    $table->bigInteger('product_id')->unsigned();
    $table->foreign('product_id')->references('id')->on('products')->onUpdate('cascade')->onDelete('cascade');
    $table->timestamps();
});

and I already put the products table before the product_exchanges table

I also made new two tables that look like this relation and get the same error

John Lobo's answer should do the trick, but additionally i would recommend to use `$table->foreignId('product_id')->constrained('products')->cascadeOnDelete()->cascadeOnUpdate();` instead, so laravel will automatically handle the fields type (in your case unsigned big integer) for you. — Tobias, Aug 20 '23 at 14:00
Interesting, works on my machine with the code you provided, did you remove both lines (the ` $table->foreign('product_id')->references('id')->on('products')->onUpdate('cascade')->onDelete('cascade');` AND the ` $table->bigInteger('product_id')->unsigned();` Line after adding mine? ` Which Laravel version are you using? — Tobias, Aug 20 '23 at 14:25
Does this answer your question? [Laravel migration (errno: 150 "Foreign key constraint is incorrectly formed")](https://stackoverflow.com/questions/47728909/laravel-migration-errno-150-foreign-key-constraint-is-incorrectly-formed) — Daniel Widdis, Aug 20 '23 at 16:10

score 1 · Answer 1 · answered Aug 21 '23 at 02:12

Based on my experience, I think it's because you wrote

    $table->bigInteger('product_id')->unsigned();

try to change it to

Schema::create('product_exchanges', function (Blueprint $table) {
    $table->id();
    $table->dateTime('date');
    $table->double('amount')->unsigned();
    $table->integer('count_from')->unsigned();
    $table->integer('count_to')->unsigned();
    $table->unsignedBigInteger('product_id')->unsigned();
    $table->foreign('product_id')->references('id')->on('products')->onUpdate('cascade')->onDelete('cascade');
    $table->timestamps();
});

or other solution is to change the id inside products table to be like this

Schema::create('products', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->text('name');   
    $table->boolean('active')->default(1);
    $table->timestamps();
});

and the other table still the same.

Let me know if this helps you.

Laravel migration (errno: 150 "Foreign key constraint is incorrectly formed") - Laravel 8

1 Answers1