Cannot migrate with foreign key in Laravel 5.2

Question

I want to create two table users and roles. My codes are below:

2014_10_12_000000_create_users_table.php

<?php

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

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->integer('role')->unsigned();
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();

            $table->foreign('role')
                ->references('id')
                ->on('roles');
        });
    }

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

2016_03_09_004256_create_roles_table.php

<?php

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

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

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

When I run php artisan migrate the following error appear.

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table trucking.#sql-1240_44 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table users add constraint u sers_role_foreign foreign key (role) references roles (id))

[PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table trucking.#sql-1240_44 (errno: 150 "Foreign key constraint is incorrectly formed")

Answer 1

You should make sure, your roles table migration is running before users table migration. By default in Laravel you have users table migration created, so if only you changed it's code and later added roles migration it won't work. You should change users or roles migration file name to make sure timestamp at the beginning of roles table migration file will be before users table.

For example you might have situation like this:

2014_10_12_000000_create_users_table.php
2015_10_12_123552_create_roles_table.php

and you should rename files to have it like this:

2015_10_12_123652_create_users_table.php
2015_10_12_123552_create_roles_table.php

Of course I assume you are using those migrations only during development and it's not on production yet.

Answer 2

 public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('firstname');
            $table->string('lastname');
            $table->text('slug');
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->decimal('fidbonus', 6, 2);
            $table->rememberToken();
            $table->timestamps();
        });
        Schema::create('Userinfos', function (Blueprint $table) {
           $table->increments('id');
           $table->integer('User_id')->unsigned();
           $table->foreign('User_id')->references('id')->on('Users');
           $table->string('address');
           $table->string('address2');
           $table->text('city');
           $table->string('zip');
           $table->string('country');
           $table->string('Phone');
           $table->timestamps();
       });
       Schema::create('password_resets', function (Blueprint $table) {
           $table->string('email')->index();
           $table->string('token')->index();
           $table->timestamp('created_at');
       });
    }

Answer 3

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('firstname');
        $table->string('lastname');
        $table->text('slug');
        $table->string('email')->unique();
        $table->string('password', 60);
        $table->decimal('fidbonus', 6, 2);
        $table->rememberToken();
        $table->timestamps();
    });
    Schema::create('Userinfos', function (Blueprint $table) {
       $table->increments('id');
       //$table->integer('User_id')->unsigned();
       $table->unsignedInteger('User_id');
       //$table->foreign('User_id')->references('id')->on('Users');
         $table->foreign('User_id')->references('id')->on('Users')->onDelete('cascade')->onUpdate('cascade');
       $table->string('address');
       $table->string('address2');
       $table->text('city');
       $table->string('zip');
       $table->string('country');
       $table->string('Phone');
       $table->timestamps();
   });
   Schema::create('password_resets', function (Blueprint $table) {
       $table->string('email')->index();
       $table->string('token')->index();
       $table->timestamp('created_at');
   });
}

try for above code.Its working fine