CODEWITHSUNDEEP
phpmysqllaravelforeign-keyseloquent
John Dorean
John Dorean

Reputation: 3874

MySQL error when creating foreign key with Laravel migration

I have a Laravel application set up and I'm using Sentry 2 for user authentication. I have a model called Post along with the default sentry User table. I would like to have a user to have many posts, and a post to belong to a user. To reflect this in my database schema, I need to create a foreign key constraint between posts and users.

The migration that I've written is the following:

public function up()
{
    Schema::table('posts', function(Blueprint $table)
    {
        $table->integer('user_id')->after('id')->nullable();
        $table->foreign('user_id')->references('id')->on('users');
    });
}

After running it with php artisan migrate, I get a MySQL error in the command prompt:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table 'blog.dev.#sql-3bb_2d' (errno: 150) (SQL: alter table posts add constraint posts_user_id_foreign foreign key (user_id) references users (id))

At first I thought this error occurred because the primary key column in users is defined differently than my foreign key column user_id, however they're both defined as int(10).

From Google I learned that this error might be caused by the two column definitions being different, however this doesn't seem to be the case here.

Upvotes: 6

Views: 5164

Answers (3)

MarcoPa84
MarcoPa84

Reputation: 21

I solved with

Schema::disableForeignKeyConstraints();

before first migration and

Schema::enableForeignKeyConstraints();

in last migration

Screenshot

Upvotes: 1

Pius T.K
Pius T.K

Reputation: 427

public function up()
{
    Schema::table('posts', function(Blueprint $table)
    {
        $table->integer('user_id')->nullable()->after('id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
    });
}

Upvotes: 0

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87719

The foreign key should already be in database, therefore I suggest to take two steps. Also I suggest to make the column user_id unsigned:

public function up()
{
    Schema::table('posts', function(Blueprint $table)
    {
        $table->integer('user_id')->after('id')->nullable()->unsigned();
    });

    Schema::table('posts', function(Blueprint $table)
    {
        $table->foreign('user_id')->references('id')->on('users');
    });
}

Upvotes: 8

Related Questions