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?

check the order of your migration

Sohel0415
– Sohel0415

2018-04-12 11:44:36 +00:00
Commented Apr 12, 2018 at 11:44 — Sohel0415
– Sohel0415, Commented Apr 12, 2018 at 11:44
Please add the migration of hazard_categories.

Jonas Staudenmeir
– Jonas Staudenmeir

2018-04-12 11:45:04 +00:00
Commented Apr 12, 2018 at 11:45 — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Apr 12, 2018 at 11:45
Do you use the InnoDB engine in both tables?

Jonas Staudenmeir
– Jonas Staudenmeir

2018-04-12 11:45:46 +00:00
Commented Apr 12, 2018 at 11:45 — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Apr 12, 2018 at 11:45
Sure, will modify question...

StuBlackett
– StuBlackett

2018-04-12 11:45:48 +00:00
Commented Apr 12, 2018 at 11:45 — StuBlackett
– StuBlackett, Commented Apr 12, 2018 at 11:45
Is there already data in hazard_videos?

Jonas Staudenmeir
– Jonas Staudenmeir

2018-04-12 11:47:20 +00:00
Commented Apr 12, 2018 at 11:47 — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Apr 12, 2018 at 11:47

Jonas Staudenmeir · Accepted Answer · 2018-04-12 11:55:56Z

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.

answered Apr 12, 2018 at 11:55

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StuBlackett Over a year ago

Great, Thank you @Jonas

Collectives™ on Stack Overflow

Laravel Migration - Foreign Key SQL Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related