Foreign key add constraint error in laravel migration

Question

When attempting to run a migration in Laravel, I encountered the following error message:

> SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
> (SQL: alter table `campaign_notifications` add constraint `campaign  
>       _notifications_campaign_id_foreign` foreign key (`campaign_id`) references `campaigns` (`id`) on delete cascade)

Below is the migration code I've written:

    Schema::create('campaign_notifications', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('campaign_id')->index();
        $table->string('user_uuid')->nullable();
        $table->string('post_id')->default(0);
        $table->tinyInteger('is_opened')->default(0);
        $table->tinyInteger('sent')->default(0);
        $table->string('payload')->nullable();
        $table->string('failed_type')->nullable();
        $table->timestamps();
        $table->foreign('campaign_id')->references('id')->on('campaigns')->onDelete('cascade');
 });

The error seems to be related to the foreign key constraint on the campaign_id column, which references the id column in the campaigns table with cascading delete behavior. However, I'm unsure why this error is occurring. Can anyone provide insights into what might be causing this issue and how to resolve it? Any help would be appreciated. Thank you!

Make sure campaign_notifications.campaign_id field is of same type as campaigns.id. If in campaigns table you use $table->increments('id'); it is unsignedBigInteger field type that would need to be added in child table structure. — Tpojka
– Tpojka, Commented Mar 30, 2020 at 15:36

Piazzi · Accepted Answer · 2020-03-30 15:28:03Z

1

First make sure you already have a campaign table in your DB (Take a look in the timestamps of your migrations), so that you can reference the table in your current migration. Basically for this migration to work you need a campaign table in your database, you can't run the campaign table migration after this one.

Also, change your campaign_id to unsigned integer

$table->unsignedInteger('campaign_id'); $table->foreign('campaign_id')->references('id')->on('campaigns')->onDelete('cascade');

answered Mar 30, 2020 at 15:28

Piazzi

2,6563 gold badges14 silver badges27 bronze badges

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

Comments

STA · Accepted Answer · 2020-03-30 14:58:31Z

0

Your migration code will look like :

Schema::create('campaign_notifications', function (Blueprint $table) {
        $table->increments('id');
        $table->string('user_uuid')->nullable();
        $table->string('post_id')->default(0);
        $table->tinyInteger('is_opened')->default(0);
        $table->tinyInteger('sent')->default(0);
        $table->string('payload')->nullable();
        $table->string('failed_type')->nullable();
        $table->bigInteger('campaign_id')->unsigned();
        $table->foreign('campaign_id')->references('id')->on('campaigns')->onDelete('cascade');
        $table->index('campaign_id');
        $table->timestamps();
 });

answered Mar 30, 2020 at 14:58

STA

35.3k9 gold badges49 silver badges62 bronze badges

1 Comment

Rwd Over a year ago

Please can you explain your answer.

Collectives™ on Stack Overflow

Foreign key add constraint error in laravel migration

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related