[Solved] SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table (alter on Foreign key)

Hello everyone,

I am facing an issue with my table migration I am getting a weird error while running

php artisan migrate:fresh

The error is

 SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'blog' (SQL: alter table `blog_comments` add constraint `blog_comments_postid_foreign` foreign key (`postID`) references `blog` (`id`) on delete cascade)

 at C:\laravel\wp4world\vendor\laravel\framework\src\Illuminate\Database\Connection.php:671
 667| // If an exception occurs when attempting to run a query, we'll format the error
 668| // message to include the bindings with SQL, which will make this exception a
 669| // lot more helpful to the developer instead of just the database's errors.
 670| catch (Exception $e) {
 > 671| throw new QueryException(
 672| $query, $this->prepareBindings($bindings), $e
 673| );
 674| }
 675|

Here is the migration file

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateBlogsTable extends Migration
{
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up(){
 Schema::create('blog', function (Blueprint $table) {
 $table->bigIncrements('id');
 $table->string('title');
 $table->string('post_url');
 $table->string('img_path')->nullable();
 $table->longtext('content');
 $table->string('tags');
 $table->string('status')->default(0);
 $table->string('public_date', 30);
 $table->unsignedBigInteger('ownerID');
 $table->foreign('ownerID')->references('id')->on('admins')->onDelete('cascade');
 $table->timestamps();
 });
 }

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

Does anyone know what is the issue about? I had checked everything and there is no error or any missing thing?
Thank you in advance

Jo Micheal

29 Apr 2021

Best Answer

Hello Joseph,
As I see that your code is missing the InnoDB command to set the engine of the database to use ‘InnoDB’, please add this line in the up function

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

so it supposes to look like this

<?php
 public function up(){
 Schema::create('posts', function (Blueprint $table) {
 $table->engine = 'InnoDB';	//Add this line
 $table->bigIncrements('id');
 $table->string('title');
 $table->string('post_url');
 $table->string('img_path')->nullable();
 $table->longtext('content');
 $table->string('caption', 400);
 $table->string('tags');
 $table->integer('views')->default(0);
 $table->integer('likes')->default(0);
 $table->string('video_iframe')->nullable();
 $table->string('status')->default(0);
 $table->string('public_date', 30);
 $table->unsignedBigInteger('ownerID');
 $table->foreign('ownerID')->references('id')->on('admins')->onDelete('cascade');
 $table->timestamps();
 });
 }
?>

Good Luck :)

Replies

Joseph Morgan

29 Apr 2021

Thank you Jo, this was a sally error, this is the first time to hear about the ‘InnoDB’

[Solved] SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table (alter on Foreign key)

Comments

Replies