CODEWITHSUNDEEP
laravelforeign-keysmigration
Evin Wijninga
Evin Wijninga

Reputation: 13

Issues with foreign constraints -

I tried to follow the documentation of laravel and laracasts, however I get an error in my migrations concerning foreign keys.

I have articles about games that are written by authors (users), so I want the user Id for the article. This is my games table:

public function up()
{
    Schema::create('games', function (Blueprint $table) {
        $table->id('id');
        $table->timestamps();
        $table->string('name', 100);
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->text('description');
        $table->string('publisher');
        $table->string('url');
    });
}

And this my user table:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

If I understand correct I name the column in the games table 'user_id', and it references to the Id of the user, which is called 'id' in the user table?

When migrating however i get the error theres no user_id.

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'user_id' doesn't exist in table (SQL: alter table gamesadd constraintgames_user_id_foreign foreign key (user_id) references users (id) on delete cascade)

Can someone tell me what I am doing wrong? Am I mixing up where I should refer to 'id' in the user table?

Upvotes: 0

Views: 41

Answers (2)

Alberto
Alberto

Reputation: 12919

Migrations by default are run with the same order that they are been created, and in your case, the games migration is been executed before the user migration, and so you when migrating the games migration, there is no user table, and so

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

Will fail.

In order to solve this problem, rename the user migration with a date before the one that has the games migration

Thanks to @lagbox for having pointed it out:
This line is not creating a new field, but is just adding a constrain

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

Instead you should first create the field and then create the constrain:

$table->bigInteger('user_id)->...;
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

Upvotes: 1

r0ulito
r0ulito

Reputation: 487

The way I usually do it is :

  • Creating the migrations files (containing all the fields)
  • Creating alterations files (adding FK)

Like that, when I run php artisan migrate the order of the migrations doesn't block anything.

With your code above you are trying to add a foreign constraint on a field which doesn't exist yet

Upvotes: 0

Related Questions