0

I try to migrate my tables but it display an error which is:

errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table users add constraint users_role_id_foreign foreign key (role_id) references roles (id))

This is my tables:

Schema::create('users', function (Blueprint $table) {
        $table->increments('id')->unsigned();
        $table->string('fullname');
        $table->string('username')->unique();
        $table->string('email')->unique();
        $table->string('password');
        $table->integer('role_id')->unsigned();
        $table->foreign('role_id')->references('id')->on('roles');
        $table->rememberToken();
        $table->timestamps();
    });


Schema::create('roles', function (Blueprint $table) {
        $table->increments('id')->unsigned();
        $table->string('name')->unique();
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        $table->timestamps();
    });


Schema::create('posts', function (Blueprint $table) {
        $table->increments('id')->unsigned();
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        $table->string('content');
        $table->string('image');
        $table->timestamps();
    });
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • 2
    Possible duplicate of [mysql Foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) – GrumpyCrouton Sep 07 '17 at 17:14
  • I understand that the foreign key column and the referencing column were not of the same type but from what i know in Laravel documentation that increments is Incrementing ID (primary key) using a "UNSIGNED INTEGER" equivalent so i make the foreign key column as unsigned integer. I want to know how to fix it since i do what they want or maybe i'm wrong –  Sep 07 '17 at 17:28
  • @FahadAlDaferi actually it's not unsigned – apokryfos Sep 08 '17 at 14:28
  • For people looking some easy solution try renaming the filename, make sure the date for the parent table migration in filename comes before the table which has the foreign key. I think migrations are executed in increasing date order. Like: 2014, then 2015 and so on. This works assuming everything else in your migration is correct. – Naveen Niraula Dec 31 '17 at 08:55

2 Answers2

0

The problem is that when you create the users table with a foreign key to the roles table, then the roles table does not exist yet, therefore you cannot add a foreign key referencing it.

I also noted that the roles table also has a foreign key referencing the users table, which makes the situation more tricky.

If you are absolutely sure that the roles table needs a foreign key to the users table, then you need to remove the foreign key creation from the users table definition, and add it separately back as alter table after the roles table is created.

However, I'm not sure that the roles table should have an fk to the users table (unless it represents the user that created the role) because you can have multiple users in a role. If you are modelling a many-to-many relationship, then this is not the way to do it. But this has got nothing to do with the current error, so treat this as a side note only.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Yes, you are right but let me explain what i want to do I have two roles 1 admin - 2 user and i want to assign one of them to each user becuase i want only admin users to login into backend dashboard as for now i make the relationship between roles and users as one(role)-to-many(users) so i need you help here –  Sep 07 '17 at 17:47
  • That's a different question from this altogether and you have to post it as such. As I explained in my answer, that was a side note. This question is about fk error. – Shadow Sep 07 '17 at 18:00
0

Sometimes that error can also occur due to migrations order. If you face errno 150 "Foreign key constraint is incorrectly formed" then go to your migrations folder and check whether the parent table migration was executed first or not. If child table migration is executed first according to the migration order, it will try to find foreign key binding and shows that error.

EcologyTom
  • 2,344
  • 2
  • 27
  • 38
abhay
  • 642
  • 5
  • 13