General error: 1824 Failed to open the referenced table

Question

I am trying to set foreign key of my 'books' table with 'categories' table using php artisan migrate, but I got the following error:

    Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'categories' (SQL: alter table `books` add constraint `books_category_id_foreign` foreign key (`category_id`) references `categories` (`id`))

books migration file:

public function up()
{
    Schema::create('books', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('category_id')->unsigned();
        $table->foreign('category_id')->references('id')->on('categories');
        $table->string("image");
        $table->string("title");
        $table->string("description")->nullable();
        $table->string("author");
        $table->string("cover");
        $table->integer("nod")->nullable();// Number of downloads
        $table->integer("rating")->nullable();
        $table->timestamps();
    });
}

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

categories migration file:

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string("title");
        $table->string("image");
        $table->timestamps();
    });
}

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

I really need help with this to use in my mobile app API. I hope someone can help me.

Answer 1

In my opinion you need to change the engine of the SQL to InnoDB, this issue was struggling with me for a lot of time all you need to do is adding

<?php 
  $table->engine = 'InnoDB';
?>

to the table migration file
reference : https://web-brackets.com/discussion/4/-solved-sqlstate-hy000-general-error-1824-failed-to-open-the-referenced-table-alter-on-foreign-key-

Answer 2

The problem is on the migration itself. Have a look carefully at this

SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'categories' (SQL: alter table `books` add constraint `books_category_id_foreign` foreign key (`category_id`) references `categories` (`id`))

You are trying to open the categories table but it basically wasn't there or wasn't created yet. If you use GUI like HeidiSQL or Navicat, or PMA, You will be able to see it.

Laravel migration takes the timestamp on the beginning of the file to decide which migration should be migrated first in sequence.

Make sure you create the categories table first before the books table (this also applies for any tables that has reference). Or simply just rename the file (change the timestamp) like E.g:

2020_01_01_1234_create_books_table.php
2020_01_01_5678_create_categories_table.php

to this

2020_01_01_1234_create_categories_table.php
2020_01_01_5678_create_books_table.php

Then run php artisan migrate:fresh to refresh your migration.

Answer 3

I faced the same issue with you since yesterday and I later saw my mistakes, I was able to understand the cause of the problem. There are so many factors to consider

1. Make sure the date for the parent table (categories) is earlier than the date for the child table (books) so that during the migration, the parent table will be created first because the child table might want to reference id in a table that does not exist.
2. Make sure to follow the convention for naming

• you can refactor your migration file like this

  $table->foreignId('category_id')->constrained('categories'); or

$table->foreignId('category_id')->constrained();

example of one of my migration files

public function up()
{
    Schema::create('project_details', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->charset = 'utf8mb4';
        $table->collation = 'utf8mb4_unicode_ci';
        $table->id();
        $table->foreignId('project_id')->constrained()->onDelete('cascade');
        $table->string('name', 150)->nullable();
        $table->string('description', 600)->nullable();
        $table->string('location', 150)->nullable();
        $table->integer('completed_percent')->nullable()->default(0);
        $table->foreignId('manager_id')->constrained('staffs');
        $table->foreignId('sponsor_id')->constrained('sponsors')->nullable();
        $table->foreignId('donor_id')->constrained('sponsors')->nullable();
        $table->foreignId('mda_id')->constrained('sponsors')->nullable();
 });
}