Unable to migrate pivot table. SQLSTATE[HY000]: General error: 1215

[bhavesh-hirani]

• Laravel Version: 7.18.0
• PHP Version: 7.39
• Laravel-5-Generators-Extended Version: 1.1.9
• Command: php artisan migrate

What I did

Created a few base tables. Have a look below:

Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('code')->unique();
            $table->string('type');
            $table->string('material');
            $table->string('colour');
            $table->text('notes');
            $table->float('price');
            $table->float('stock');
            $table->enum('unit',['SQM', 'LM', 'KG', 'L', 'PCS']);
            $table->timestamps();
        });

Schema::create('quotes', function (Blueprint $table) {
            $table->id();
            $table->integer('customer_id')->unsigned();
            $table->integer('user_id')->unsigned();
            $table->float('total');
            $table->boolean('tax_inclusive')->default(1);
            $table->text('notes');
            $table->timestamps();
        });

Then I ran php artisan make:migration:pivot products quotes which created the migration file. Below is the pivot migration

Schema::create('product_quote', function (Blueprint $table) {
            $table->integer('product_id')->unsigned()->index();
            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
            $table->integer('quote_id')->unsigned()->index();
            $table->foreign('quote_id')->references('id')->on('quotes')->onDelete('cascade');
            $table->primary(['product_id', 'quote_id']);
            $table->float('price');
            $table->float('qty');
            $table->text('product_notes');
        });

What I expected to happen

Successfully run the migration.

What happened

The error below is given when running php artisan migrate

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `product_quote` add constraint `product_quote_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete cascade)

What I've already tried to fix it

??

[pxpm]

Hello @bhavesh-hirani

My guess is that is failing because product_id id has already been indexed with index().

Could you try removing ->index() from product_id definition ?

Best, Pedro

[bhavesh-hirani]

But the pivot migration code was generated by Laravel Generators. I did not change anything except adding a few extra columns. Also in previous Laravel version like 5.7, this worked without any issues.

I gave that a try and comes back with the same error.

I am using MySQL V5.7.26. DB Encoding is utf8mb4 and collation is utf8mb4_general_ci.

Looks like something else. I tried in a fresh Laravel install and same thing happens.

[bhavesh-hirani]

Can someone please help me on this?

[bhavesh-hirani]

Ok. So this is what fixed it. When I created the tables products and quotes using php artisan make:migration, a migration was created with $table->id(); by default for both tables. Previously in Laravel 5&6, that would create a column of INT. But in Laravel 7 BIGINT is used for id. And foreign key was not working for BIGINT. Once I changed the data type to INT, everything worked just fine.

[hirani89]

Something more. https://github.com/laravel/framework/issues/33642

According to that, BIGINT has been the default for id for 2 years now. So that means that migration code which is generated by the generators should match the fields as well. Any chance they can be BIGINT too?

[tabacitu]

I've just launched version 2.0.0 which makes bigInteger the default. So this should be fixed in 2.0.0.

If anybody else experiences this in 2.x please let me know and we'll reopen.

Cheers!

PS. Obviously sorry for the delayed response guys, this repo hasn't been a priority for me. I'd love to see more help from the community here, I think it's totally doable to take this to 0 issues and 0 PRs since this project's scope is very limited. I'd sleep a lot better then 😄 If anybody can help - by reviewing and testing existing PRs, or creating new PRs, please do, your help will be very appreciated.