Laravel 5.4: SQLSTATE[HY000]: General error: 1005 Can't create table "Foreign key constraint is incorrectly formed"

Question

I'm using Laravel 5.4 and I have added this Migration:

public function up()
    {
        Schema::create('episodes', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('course_id')->unsigned();
            $table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');
            $table->string('type', 10);
            $table->string('title');
            $table->string('slug');
            $table->text('description');
            $table->text('body');
            $table->string('videoUrl');
            $table->string('tags');
            $table->string('time', 15)->default('00:00:00');
            $table->integer('number');
            $table->integer('viewCount')->default(0);
            $table->integer('commentCount')->default(0);
            $table->integer('downloadCount')->default(0);
            $table->timestamps();
        });
    }

Now when I run php artisan migrate, I get this error:

SQLSTATE[HY000]: General error: 1005 Can't create table elearning.episodes (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table episodes add constraint episodes_course_id_foreign foreign key (course_id) references courses (id) on delete cascade)

I also tried this but still gets the same error:

$table->unsignedBigInteger('course_id');

So how can I properly run this Migration? I'm really stuck with this, please help me out...

USERS MIGRATTION:

Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('level')->default('user');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

Course Migration:

Schema::create('courses', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->string('type', 10);
            $table->string('title');
            $table->string('slug');
            $table->text('description');
            $table->text('body');
            $table->string('price',50);
            $table->string('imageUrl');
            $table->string('tags');
            $table->string('time', 15)->default('00:00:00');
            $table->integer('viewCount')->default(0);
            $table->integer('commentCount')->default(0);
            $table->timestamps();
        });

there are so many questions already about this error. have you tried to find a solution from those?? — zahid hasan emon, Dec 29 '21 at 11:50
@zahidhasanemon I have tried them but couldn't solve the issue :(( — memite7760, Dec 29 '21 at 11:50
then share your course table migration and order of migration in the question. — zahid hasan emon, Dec 29 '21 at 11:52
@zahidhasanemon I also tried `bigIncrements` instead of `increments` but still gets the error — memite7760, Dec 29 '21 at 11:52
@zahidhasanemon I just added the `users` migration which is related this, plz check it out — memite7760, Dec 29 '21 at 12:00
it seems the order of migration is the issue. as i asked in the previous comment share the course migration, not the user. and the order of migrations as well. — zahid hasan emon, Dec 29 '21 at 12:01
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/240530/discussion-between-memite7760-and-zahid-hasan-emon). — memite7760, Dec 29 '21 at 12:01
Courses can be created succesffully. But I don't know whats the problem with episdes — memite7760, Dec 29 '21 at 12:11
Does this answer your question? [General error: 1005 Can't create table ,Foreign key constraint is incorrectly formed in laravel](https://stackoverflow.com/questions/41259651/general-error-1005-cant-create-table-foreign-key-constraint-is-incorrectly-fo) — Junior, Dec 31 '21 at 10:59

Giles Bennett · Accepted Answer · 2021-12-29T12:09:49.757

0

Rather than use :

$table->increments('id');

you should use :

$table->id();

(it's just easier).

To create your foreign relationships, rather than have

$table->integer('course_id')->unsigned();
$table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');

you can just use :

$table->foreignId('course_id')->constrained()->cascadeOnDelete();

which will automatically create a column of the right type (unsigned big integer) and then create the relationship by looking for the id column on the courses table.

EDIT

As you're using an older version of Laravel, you cannot use the id() function, so just create the course_id column as a big integer :

$table->bigInteger('course_id')->unsigned();

and then create your relationship as before.

edited Dec 29 '21 at 12:09

answered Dec 29 '21 at 11:53

Giles Bennett

1,509
1
12
15

If I use `$table->id();` I will get `Call to undefined method Illuminate\Database\Schema\Blueprint::id()` because my version is **5.4** – memite7760 Dec 29 '21 at 11:58
OK - you need to check what using $table->increments('id'); creates - if it creates a big integer for the ID column on your courses table, then your course_id column on your episodes table needs to also be a big integer, in which case your first line should be $table->bigInteger('course_id')->unsigned(); – Giles Bennett Dec 29 '21 at 12:01

score 0 · Answer 2 · answered Dec 29 '21 at 12:16

One problem maybe you are not making unsignedBigInteger as course_id

 Schema::create('episodes', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedBigInteger('course_id')->index('course_id');  
        $table->string('type', 10);
        $table->string('title');
        $table->string('slug');
        $table->text('description');
        $table->text('body');
        $table->string('videoUrl');
        $table->string('tags');
        $table->string('time', 15)->default('00:00:00');
        $table->integer('number');
        $table->integer('viewCount')->default(0);
        $table->integer('commentCount')->default(0);
        $table->integer('downloadCount')->default(0);
        $table->timestamps();

        $table->index( [ 'created_at', 'updated_at' ] );

       //Constraint
        $table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');

   });

OR

The second problem may be you are not making your migrations is not ruing sequence, for example, first users table need to be run then courses then episodes

What does `$table->index( [ 'created_at', 'updated_at' ] );` mean here? — memite7760, Dec 29 '21 at 12:19
Make your created_at and updated_at as an index so your data can retrieve faster — Syed Ali, Dec 29 '21 at 12:34

Laravel 5.4: SQLSTATE[HY000]: General error: 1005 Can't create table "Foreign key constraint is incorrectly formed"

2 Answers2