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.

Caravel

I had this issue today because I was connecting a InnoDB engine table to a MyISAM engine table. Changed MyISAM to InnoDB and they connected right away. Also, the parent table <inside on('table')> needs to exist first. – Oza

Check if your reference table timestamp is before the new migration. Otherwise, refactor the migration timestamps – Seizing

Answer 1

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.

Fariss

Hi! Looking back to the past because my answer seems to help for many people. I just want to tell you to be careful about running the php artisan migrate:fresh command. Make sure you don't do it in production. Otherwise, your data will be lost. It would be a good idea to backup your data first if you really need to run it in production. – Fariss

Answer 2

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

Erdrich

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

Idiotic

Hello, how can I change the date so that the referenced table is earlier ?! – Tosspot

rename the migrations file, see my migration files !migration files Check the my migration files from the link, you will discover that the ones up, like the project table, I rename them to 2019 manually – Idiotic

Answer 4

When you're setting up the foreign key $table->foreign('category_id')->references('id')->on('categories'); the table 'categories' doesnt exist yet.

Change the migration name of the categories to be run before that of the books one.

like:

2021_08_11_121933_create_books_table.php
2021_08_12_121933_create_categories_table.php

instead of

2021_08_11_121933_create_categories_table.php
2021_08_12_121933_create_books_table.php

Sidwohl

Exactly, I had the same problem and always be sure to create all the tables in the correct order when working with Laravel. – Frumpy

Answer 5

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-

Kostman

Interesting! I changed by this: $table->engine = 'MyISAM'; – Nathalienathan

it's depend on the engine of your DB – Kostman

Answer 6

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.

Bookbinding

You may add some details about what did and command you ran and possibly adapt it to Veasna WT problem – Duty

Answer 7

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.

Canvas

Answer 8

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.

Hynes

Answer 9

You can do this:

DB::statement('SET FOREIGN_KEY_CHECKS=0;');

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

DB::statement('SET FOREIGN_KEY_CHECKS=1;');

I did this DB::statement('SET FOREIGN_KEY_CHECKS=0;'); to turn off and 1 to turn on. It worked! This solution is better for the specified problem.

Wader

Answer 10

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

Catalepsy

Answer 11

U must first create migration file for referenced table e.g

1. product migration
2. orders migration

than u can use php artisan migrate with orders relation to product. if u create like this:

1. orders migration
2. product migration

that will throw error filed to open referenced table

Purposive