General error: 1824 Failed to open the referenced table

user_12520085 0
Viewed 24399

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.

laravel migrate migration foreign-keys reference
8 Answers

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.

user_110801700

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();
 });
}
user_115350320

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

user_92142600

I had the same error just right now.

It has something related to creating those migrations.

The problem I had because I refactored the table name manually and didn't take care of the connection between the tables and instantly tried to migrate them.

The solution I did and worked for me is I deleted the migrations manually and created them again using php make:magirate.

user_113524560
user_100716690

The table (Categories) you are referencing its "id" is either not created or its created after the "books" table. You can manually delete both tables and create them again with "Categories" been first or you can manually change the date of the "Categories" to a date before the "books" and you are good to go.

user_122226370

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");

user_152619790

Make sure both the tables are created using the same engine. In my case, I created two tables with two different engines (MyISAM and InnoDB). Changing both the table engines to InnoDB did the trick.

user_79611490
Related