Foreign key constraint is incorrectly formed in MySQL: Laravel migration

Question

My migrations are

Schema::create('user_details', function (Blueprint $table) {
    $table->Increments('id')->unique();
    $table->text('detail');
    $table->text('value');
    $table->integer('user_id');
    $table->timestamps();

    $table->foreign('user_id')->references('id')->on('users');
});

and

Schema::create('users', function (Blueprint $table) {
    $table->Increments('id')->unique();
    $table->integer('user_detail_id');
    $table->integer('user_setting_id');
    $table->integer('company_id');
    $table->mediumText('role');
    $table->mediumText('email')->unique();
    $table->mediumText('password');
    $table->timestamps();
});

When I try to migrate, I'm getting this error message:

errno: 150 "Foreign key constraint is incorrectly formed" (SQL: alter table user_details add constraint user_details_user_id_foreign fore ign key (user_id) references users (id))

Why is this happening?

Answer 1

You need to set the InnoDB storage engine for your tables. You can either do it in schema builder: $table->engine = 'InnoDB'; or in config/database.php file (connections => mysql => engine): change 'engine' => null, to 'engine' => 'InnoDB',.

Second, primary keys which are defined like $table->increments('id'); are auto-incrementing UNSIGNED INTEGER. And your foreign keys should be unsigned integer. Read more about creating columns.

Schema::create('user_details', function (Blueprint $table) {
    $table->engine = 'InnoDB';

    $table->increments('id');
    $table->text('detail');
    $table->text('value');
    $table->unsignedInteger('user_id');
    $table->timestamps();

    $table->foreign('user_id')->references('id')->on('users');
});

Schema::create('users', function (Blueprint $table) {
    $table->engine = 'InnoDB';

    $table->increments('id');
    $table->integer('user_detail_id');
    $table->integer('user_setting_id');
    $table->integer('company_id');
    $table->mediumText('role');
    $table->mediumText('email')->unique();
    $table->mediumText('password');
    $table->timestamps();
});