CODEWITHSUNDEEP
mysqllaraveleloquent
Robert Ross
Robert Ross

Reputation: 1189

errno: 150 "Foreign key constraint is incorrectly formed" when trying to create a foreing key

I have two tables - Users and Points. In Points I want to have the following columns : id, points, user_id(foreign key).

For the purpose I did the below in the following order :

Created I migration looking like this :

public function up()
    {
        Schema::create('points', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('points')->unsigned();
            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('user');
        });
    }

After I ran the migration I got the following error :

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table logos.#sql-3da4_79a (errno: 150 "Foreign key constraint is incorrectly formed ") (SQL: alter table points add constraint points_user_id_foreign foreign key (user_id) references user (id))

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table logos.#sql-3da4_79a (errno: 150 "Foreign key constraint is incorrectly formed ")

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

Afterwards, I added this relationship to users :

 public function points()
    {
        return $this->hasOne('App\Points');
    }

And this relationship to points : 

 public function user()
    {
        return $this->hasOne('App\User', 'user_id');
    }

Then I added another migration solely for the foreign key(since the previous migration created the table and its columns despite the error) : 

   public function up()
{
     Schema::table('points', function (Blueprint $table) {

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

  });
}

After running the migration I got the same error as the first time. I know that the error seems "self explenatory", but honestly it doesn't make any sense to me. I am simply following the official documentation and I don't understand why am I getting this error.

Can somebody explain and help me troubleshoot?

Cheers!

Upvotes: 0

Views: 1977

Answers (1)

Leo
Leo

Reputation: 7420

First of, make sure your timestamp for creating users its before you run the points migration!

So users should migrate first, before points. Since you want to make sure you have users table already before trying to declare a foregin key!

Then you have a typo on foreign statement It should be users instead of user like below:

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

Upvotes: 2

Related Questions