Framework: [5.3]migrate get error: 1215 if add foreign key constraint in users table

[ac1982]

same schema in users table and products table

$table->integer('company_id')->unsigned()->nullable();
$table->foreign('company_id')->references('id')->on('companies');

It works fine in others table like products table, but get error in users table

  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `users` add constraint `users_c
  ompany_id_foreign` foreign key (`company_id`) references `companies` (`id`))



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

Actually, I can add foreign key constraint in mysql console manually.
So, it looks like be caused by laravel.

[themsaid]

@ac1982 please share the schema of the users table, the two columns of relation must be identical.

Also please use the issue template that was added by default when you first opened the issue, it helps us collect full details.

Thanks.

[ac1982]

It is not necessary. One works fine, but another not, it would be wrong.

[themsaid]

It looks like the query generated by laravel is correct:

alter table `users` add constraint `users_company_id_foreign` foreign key (`company_id`) references `companies` (`id`)

That's why I assume something is wrong on the other side of the relation.

Can you confirm that the above query is the one you run in the console? exact.

[ac1982]

users tabel

        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); 
            $table->string('phone')->nullable();
            $table->string('email')->nullable()->unique();
            $table->string('address')->nullable();
            $table->integer('company_id')->unsigned()->nullable();
            $table->foreign('company_id')->references('id')->on('companies'); 
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        }); 
//get error

contracts table

Schema::create('contracts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned()->nullable();
            $table->foreign('user_id')->references('id')->on('users')->onUpdate('cascade')->onDelete('set null');
            $table->integer('company_id')->unsigned()->nullable();
            $table->foreign('company_id')->references('id')->on('companies');
            $table->decimal('amount', 12, 2)->nullable();
            $table->tinyInteger('payment_status')->nullable();
            $table->timestamps();
        });
//works fine

companies table

Schema::create('companies', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); 
            $table->string('license')->nullable();
            $table->string('address')->nullable();
            $table->string('website')->nullable();
            $table->string('hotline')->nullable();
            $table->string('bank')->nullable();
            $table->string('account')->nullable();
            $table->json('contact')->nullable(); 
            $table->timestamps();
        });

[themsaid]

You have to create the tables in this order:

• companies table
• users table
• contracts table

[ac1982]

Yes. It works if created them in orders. I did not notice that. Thank you @themsaid .

[themsaid]

Welcome :)

[ghost]

@themsaid thanks a lot I was finding the same thing

[david95thinkcode]

Worked for me.
Solution : Create tables without foreign key first.

[chavdar-ang]

Check the date on the migrations file (e.g. 2017_11_23_112220_create_products_table). Laravel is making the migrations based on that date so it is possible to run the foreign constraint before the table was even created. At least that was in my case. You can rename the migration file to change the execution.