Cannot add foreign key constraint in Laravel Migration

1
votes

I've found dozens of similar issues posted, but none of the solutions have seemed to work in my case. When I run "php artisan migrate:fresh", it's throwing the error...

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table slicer_profiles add constraint slicer_profiles_user_id_foreign foreign key (user_id) references users (id) on delete cascade)

  • All tables created are InnoDB
  • The 'users' table is being created before my table

  • I've split the code in to two steps, assigning the foreign key afterwards

    Schema::create('slicer_profiles', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned()->index();
    $table->string('title');
    $table->text('description');
    $table->string('slicer');
    $table->string('machine');
    $table->softDeletes();
    $table->timestamps();
});

Schema::table('slicer_profiles', function($table) {
    $table->foreign('user_id')->unsigned()
        ->references('id')
        ->on('users')
        ->onDelete('cascade');
});

I check the auth users table and it seems to use an UNSIGNED BIGINT, so I try setting ->unsigned() on the reference as well, but no change. Any help solving this would be greatly appreciated!

1
phpmysqllaravelforeign-keysdatabase-migration

1 Answers

2
votes

If the users.id field is a BIGINT then your need to make the users_id column on slicer_profiles a BIGINT so that the 2 fields have exact matching types.

Schema::create('slicer_profiles', function (Blueprint $table) {
    ...
    $table->bigInteger('user_id')->unsigned()->index();
    // or $table->unsignedBigInteger('user_id')->index();
    ...
});