I am new to laravel and to programming in general and am having problem migrating on my laravel project. I am trying to create a database for FAQ with one to many relations between the tables 'questions' and 'question_category'. I cant seem to migrate it without errors. Here is the error below,

SQLSTATE[HY000]: General error: 1822 Failed to add the foreign key constraint. Missing index for constraint 'questions_question_category_id_foreign' in the referenced table 'question_category' (SQL: alter table `questions` add constraint `questions_question_category_id_foreign` foreign key (`question_category_id`) references `question_category` (`id`) on delete cascade)

If someone can help it will be of great help. Thankyou.

question_category table/migration

        Schema::create('question_category', function (Blueprint $table) {
            $table->integer('id');
            $table->string('question_category_title');
            $table->mediumText('description')->nullable();
            $table->timestamps();
        });

questions table/migration

        Schema::create('questions', function (Blueprint $table) {
            $table->increments('id');
            $table->string('question');
            $table->mediumText('answer')->nullable();
            $table->timestamps();
            $table->integer('question_category_id');
            $table->foreign('question_category_id')
                ->references('id')
                ->on('question_category')
                ->onDelete('cascade');
        });

CodePudding user response：

change datatype of the foreign key field in the questions table

$table->unsignedBigInteger('question_category_id');

change primary key datatype in question category table

$table->bigIncrements('id');

CodePudding user response：

Replace it with questions table/migration

Schema::create('questions', function (Blueprint $table) {
            $table->increments('id');
            $table->string('question');
            $table->mediumText('answer')->nullable();
            $table->timestamps();
            $table->integer('question_category_id')->unsigned();
            $table->foreign('question_category_id')
                ->references('id')
                ->on('question_category')
                ->onDelete('cascade');
        });

Now dump both table as question_category and questions or use php artisan migrate:fresh (which will remigrate your tables) and check it. Thank you.