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.

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 needs to exist first. — AyB, Feb 10 '21 at 13:28
Check if your reference table timestamp is before the new migration. Otherwise, refactor the migration timestamps — Olotin Temitope, Oct 03 '22 at 15:45

Yura · Answer 1 · 2022-03-10T08:32:50.787

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.

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. — Yura, Jul 27 '23 at 14:42

Dmitriy Ivanov · Answer 2 · 2020-09-04T10:40:21.033

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

KINGSLEY OKPARA · Answer 3 · 2020-09-23T08:46:29.217

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

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

Hello, how can I change the date so that the referenced table is earlier ?! — Tarek AS, Oct 06 '20 at 08:42
rename the migrations file, see my migration files ![migration files](https://res.cloudinary.com/kingsconsult/image/upload/v1602086123/migration_esmzsn.png) 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 — KINGSLEY OKPARA, Oct 07 '20 at 15:56

score 2 · Answer 4 · edited Aug 05 '20 at 16:06

2

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.

edited Aug 05 '20 at 16:06

Lee Goddard

10,680
4
46
63

answered Apr 16 '20 at 22:55

Rose Riyadh

518
6
16

You may add some details about what did and command you ran and possibly adapt it to Veasna WT problem – XavM Apr 17 '20 at 09:03

score 2 · Answer 5 · answered May 12 '21 at 13:46

2

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-

answered May 12 '21 at 13:46

Mohamed Atef

115
8

1

Interesting! I changed by this: $table->engine = 'MyISAM'; – fcva Oct 05 '21 at 18:03
it's depend on the engine of your DB – Mohamed Atef Oct 06 '21 at 19:19

score 1 · Answer 6 · answered Dec 12 '21 at 11:00

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.

score 0 · Answer 7 · answered Jun 15 '22 at 04:43

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

score 0 · Answer 8 · answered Jul 30 '22 at 16:48

0

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.

answered Jul 30 '22 at 16:48

Rony Emrul

21
4

score 0 · Answer 9 · answered Mar 18 '23 at 03:45

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

product migration
orders migration

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

orders migration
product migration

that will throw error filed to open referenced table

score 0 · Answer 10 · answered Aug 27 '23 at 04:38

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

score 0 · Answer 11 · answered Aug 28 '23 at 06:12

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.

General error: 1824 Failed to open the referenced table

11 Answers11

Linked