Laravel Migration - Foreign Key SQL Error

Question

I have a hazard_categories table, Which contains a hazard_category_id

and in my hazard_videos table I'd like to reference it.

My hazard categories migration is as follows :

Schema::create('hazard_categories', function (Blueprint $table) {
 $table->increments('hazard_category_id');
 $table->string('hazard_category_name');
 $table->string('hazard_category_thumb');
 $table->integer('hazard_category_total');
 $table->timestamps();
 $table->softDeletes();
 });

I've written my migration as follows :

 Schema::table('hazard_videos', function (Blueprint $table)
 {
 $table->integer('video_category')->unsigned();
 $table->foreign('video_category')->references('hazard_category_id')->on('hazard_categories');
 });

But when I run this, I get the MySQL Error :

[Illuminate\Database\QueryException]
 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`icourse`.`#sql-da4_40df`, CONSTRAINT `hazard_videos_video_category_foreign` FOREIGN KEY (`video_category`) REFERENCES `hazard_categorie
 s` (`hazard_category_id`)) (SQL: alter table `hazard_videos` add constraint `hazard_videos_video_category_foreign` foreign key (`video_category`) references `hazard_categories` (`hazard_category_id`))

Why would I get this? I've mirrored the Laravel docs, But hit an MySQL Error.

Is there a better way to write my referential integrity constraint?

• Sohel0415: check the order of your migration
• Jonas Staudenmeir: Please add the migration of hazard_categories.
• Jonas Staudenmeir: Do you use the InnoDB engine in both tables?
• StuBlackett: Sure, will modify question...
• Jonas Staudenmeir: Is there already data in hazard_videos?

Answer 1

If there is already data in your table, the values of video_category have to be valid foreign keys:

Schema::table('hazard_videos', function (Blueprint $table) {
 $table->integer('video_category')->unsigned();
});
HazardVideo::query()->update(['video_category' => 1]);
Schema::table('hazard_videos', function (Blueprint $table) {
 $table->foreign('video_category')->references('hazard_category_id')
 ->on('hazard_categories');
});

Or you make the column nullable.

• StuBlackett: Great, Thank you @Jonas