General error 1824 failed to open the referenced table

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

    IlluminateDatabaseQueryException 

  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.

asked Mar 24, 2020 at 7:05

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.

answered Nov 28, 2020 at 1:20

You try get category for book before you create category table and book table cant understand what you referenced for.

Solution #1

Declare your category table before book table, just rename date in migration file name. Category table must be created before book table.

Solution #2

Create reference after you create category table.

Remove
$table->foreign('category_id')->references('id')->on('categories');
from book migration and create references after you up category table.

categories migration file:

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

    Schema::table('books', function (Blueprint $table) {
        $table->foreign('category_id')->references('id')->on('categories');
    });
}
    

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

Main idea is make all relations when all tables created.
Also, use php artisan migrate:fresh for fast wipe and recreate tables

answered Sep 4, 2020 at 8:51

answered Dec 12, 2021 at 11:00

Make sure you have such a table in your database. If you use a lot of database, then you need to specify the database for constrained

$connection_db = DB::connection(‘connection_name’)->getDatabaseName();
$table->foreign(‘category_id’)->references(‘id’)->on(«$connection_db.categories»);

answered Jun 15, 2022 at 4:43

answered Jul 30, 2022 at 16:48