E
E
Eternal972021-02-09 18:33:27
Database migration
Eternal97, 2021-02-09 18:33:27

What could be the cause of the SQLSTATE[HY000]: General error: 1005 error when migrating a database?

Good day to all. An error occurs when running migrations of this kind:

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1005 Can't create table
 `check_lists`.`tasks` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `tasks` add
 constraint `tasks_checklist_id_foreign` foreign key (`checklist_id`) references `checklists` (`id`) on delete
 cascade)

On the Internet, I found a lot of advice, but none of them helped. The structure of my migrations is as follows:
1. Users table:
<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    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();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

2. Table checklists:
<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateChecklistsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('checklists', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')
                ->references('id')->on('users')
                ->onDelete('cascade');
            $table->string('title');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('checklists');
    }
}

3. Tasks table:
<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTasksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('tasks', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('checklist_id');
            $table->foreign('checklist_id')
                ->references('id')->on('checklists')
                ->onDelete('cascade');
            $table->string('message');
            $table->boolean('completed');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('tasks');
    }
}

Relationships in the models were prescribed as follows:
1. Model User:
<?php

namespace App;

use Illuminate\Notifications\Notifiable;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function checklists() 
    {
        return $this->hasMany(Checklist::class);
    }
}

2. Checklist model:
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Checklist extends Model
{
    protected $guarded = [];

    public function user ()
    {
    	return $this->belongTo(User::class);
    }

    public function tasks()
    {
        return $this->hasMany(Task::class);
    }
}

3. Task model:
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Task extends Model
{
    protected $guarded = [];

    public function checklist ()
    {
    	return $this->belongTo(Checklist::class);
    }
}

The order of running migrations before the error is as follows:
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.08 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.07 seconds)
Migrating: 2021_02_01_171453_create_tasks_table

If I remove the foreign key constraint, then the migrations are successful.
What can be a jamb? Thank you very much in advance!

Answer the question

In order to leave comments, you need to log in

2 answer(s)
V
VicTHOR, 2021-02-09
@Eternal97

bigIncrements and unsignedBigInteger are different types of columns, one cannot refer to the other.
Either both cells are unsigned, or both are not unsigned.

S
Sanes, 2021-02-09
@Sanes

Try

$table->foreignId('checklist_id')
      ->constrained()
      ->onDelete('cascade');

Instead
$table->unsignedBigInteger('checklist_id');
$table->foreign('checklist_id')
          ->references('id')->on('checklists')
          ->onDelete('cascade');

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question