Laravel 5.3 - Foreign key constraint is incorrectly formed

Question

Using Laravel 5.3 and Eloquent model, Trying to make a user role model. the main schema that I have 2 tables users and roles, each user have a role and every role have many users (one to many relationship).

when i'm trying to make a foreign key in user table that references the id in roles table it gives me this error

In Connection.php line 647:

SQLSTATE[HY000]: General error: 1005 Can't create table mydata.#sql-7e0_71 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table "user" add constraint "user_role_id_foreign" foreign key ("role_id") references roles("id"))

In Connection.php line 449:

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

Note: I'm using artisan command migrate, and I've tried these answers 1,2

here is my up() function for my user migration

public function up()
    {
        Schema::create('user', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->integer('role_id')->unsigned();
            $table->string('remember_token')->nullable();
            $table->timestamps();

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

and for role table

public function up()
{
    Schema::create('role', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('description');
        $table->timestamps();
    });
}

Possible duplicate of Laravel Migration Foreign key constraint is incorrectly formed — Wahyu Kristianto
I changed the file name of the migration to change the order and it's working fine now — Poula Adel

Marwelln Marwelln · Accepted Answer · 2018-06-09T21:28:50

You are trying to add a foreign key that have a reference on the roles table (->on('roles')), but in your migration file you are creating a table named role (Schema::create('role', ...). You need to match those.

Either changed the name of the role table to roles or your reference to the role table.

Laravel 5.3 - Foreign key constraint is incorrectly formed

1 Answers