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?

the field types have to match exactly ... `increments` creates an unsigned integer, you are creating just an integer field (user_id), needs to also be unsigned ... and assuming `users` migration is before `user_details` — lagbox, Dec 11 '19 at 08:04
Does this answer your question? [Laravel migration: "Foreign key constraint is incorrectly formed" (errno 150)](https://stackoverflow.com/questions/32669880/laravel-migration-foreign-key-constraint-is-incorrectly-formed-errno-150) — Collin, Dec 11 '19 at 08:05

N'Bayramberdiyev · Answer 1 · 2019-12-11T08:43:59.860

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();
});

Foreign key constraint is incorrectly formed in MySQL: Laravel migration

1 Answers1