Problem with error: 1215 Cannot add foreign key constraint

Question

How to solve this error? SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table invoices add constraint invoices_form_id_foreign foreign key (form_id) references forms (id) on delete cascade)

I get it after: php artisan migrate:fresh

For foreign keys in invoices get this error. 3 foreign keys generate error but one for user_id is good and works fine. I tried all solutions and didn't worked. Please help me.

2014_10_12_000000_create_users_table.php

    <?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('surname')->nullable();
            $table->string('showname')->nullable();
            $table->string('business')->nullable();
            $table->string('NIP')->nullable();
            $table->string('PESEL')->nullable();
            $table->string('address')->nullable();
            $table->string('city')->nullable();
            $table->string('postalcode')->nullable();
            $table->string('phone')->nullable();
            $table->string('comments')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

2020_07_23_104440_invoices.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Invoices extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('invoices', function (Blueprint $table) {
            $table->increments('id');
            $table->string('invoicenumber')->nullable();
            $table->date('invoicedate')->nullable();
            $table->date('selldate')->nullable();
            $table->integer('user_id')->unsigned()->nullable();
            $table->integer('form_id')->unsigned()->nullable();                        
            $table->integer('currency_id')->unsigned()->nullable();
             
            $table->integer('proform_id')->unsigned()->nullable(); 
            $table->string('paymentmethod')->nullable();
            $table->date('paymentdate')->nullable();
            $table->string('status')->nullable();
            $table->string('comments')->nullable();
            $table->string('city')->nullable();
            $table->string('paid')->nullable();
            $table->string('autonumber')->nullable();
            $table->string('automonth')->nullable();
            $table->string('autoyear')->nullable();
            $table->timestamps();
        });
        
        Schema::table('invoices', function (Blueprint $table){
            $table->foreign('user_id')
                  ->references('id')
                  ->on('users');
            
            $table->foreign('form_id')
                  ->references('id')
                  ->on('forms');

            $table->foreign('currency_id')
                  ->references('id')
                  ->on('currencys');
            
            
            
            $table->foreign('proform_id')
                  ->references('id')
                  ->on('proforms');

        });
        
        
        
        
        
        
    }


    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('invoices');
    }
}

2020_07_27_090356_proforms.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Proforms extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('proforms', function (Blueprint $table) {
            $table->increments('id');
            $table->string('proformnumber')->nullable();
            $table->date('proformdate')->nullable();
            $table->date('selldate')->nullable();
            $table->integer('user_id')->unsigned()->nullable();
            $table->string('paymentmethod')->nullable();
            $table->date('paymentdate')->nullable();
            $table->string('status')->nullable();
            $table->string('comments')->nullable();
            $table->timestamps();
        });
        
        Schema::table('proforms', function (Blueprint $table){
            $table->foreign('user_id')
                  ->references('id')
                  ->on('users');
        });
        
        
        
        
        
        
    }


    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('proforms');
    }
}

2020_07_28_091856_forms.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Forms extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('forms', function (Blueprint $table) {
            $table->increments('id');
            $table->string('form')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('forms');
    }
}

2020_07_28_091919_currencys.php

    <?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Currencys extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('currencys', function (Blueprint $table) {
            $table->increments('id');
            $table->string('currency')->nullable();
            $table->string('course')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
         Schema::dropIfExists('currencys');
    }
}

Answer 1

Here is what might be happening with your migrations.

1. Check the the primary and foreign key type. If user primary key ID is type $table->increments('id'); // bigInteger, then the foreign key should be bigInteger as well $table->bigInteger('user_id');

2. Please check your migration order. For example table invoices has foreign key fromforms table. During the migration, forms table should migrate first. And to make it make it happen, just rename the migration file(numeric section of file name) so that the forms.

Answer 2

because when excuting the migration reach this line:

 $table->foreign('form_id')->references('id')->on('forms');

in 2020_07_23_104440_invoices file the forms table has not been created yet

you should make a new migration to make that relation

 Schema::table('invoices', function (Blueprint $table){ 
            $table->foreign('form_id')
                  ->references('id')
                  ->on('forms');
}
 

this migration should be after 2020_07_28_091856_forms.php migration ...

Answer 3

The problem is the order of the migration

see the order

2020_07_23_104440_invoices.php

2020_07_28_091856_forms.php

when the invoices table is created there will be no forms table as it is created afterwards. So the error is because you are trying to create relationship between non existing forms table id field while creating invoices table.

What you can do is create a new migration file for attaching the relationship only.

here is how to do it delete add foreign key from. remove this part from 2020_07_23_104440_invoices.php

$table->foreign('form_id')
                      ->references('id')
                      ->on('forms');

after removing create seperate migration for adding forign key

run
php artisan make:migration add_form_foreign_key_to_invoices

It will generate the new migration file. Update it like this.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddFormForeignKeyToInvoices extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('invoices', function (Blueprint $table) {
            $table->foreign('form_id')->references('id')->on('forms');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('invoices', function (Blueprint $table) {
            $table->dropForeign('form_id');
        });
    }
}