SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

[jackwander]

• Laravel Version: 5.8.1
• PHP Version: 7.1
• Database Driver & Version: MySQL 5.7.22-0ubuntu0.17.10.1

Description:

My code for inserting FK is working on 5.7 but not in 5.8.

[bicibg]

Just found the problem. if the "id" on first table is "bigIncrements", "user_id" on the new table needs to be "unsignedBigInteger". Similarly, if "id " is "increments" then "user_id" must be "unsignedInteger"

[Behinder]

I fix it by changing foreign key from unsignedInteger to unsignedBigInteger

in Laravel 6 this does not work. I have contacts and firms: public function up() { Schema::create('contacts', function (Blueprint $table) { $table->BigIncrements('id'); $table->string('name'); $table->string('surname'); $table->string('email'); $table->string('title')->nullable(); $table->text('note')->nullable(); $table->string('country')->nullable(); $table->string('phone')->nullable(); $table->timestamps(); $table->unsignedBigInteger('firm_id');

        $table->foreign('firm_id')->references('id')->on('firms')->onDelete('cascade');

    });
}

public function up() { Schema::create('firms', function (Blueprint $table) { $table->BigIncrements('id'); $table->string('name'); $table->string('abbreviation')->nullable(); $table->string('address'); $table->string('country')->default('USA'); $table->string('webpage'); $table->timestamps(); }); }

And i still got : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter tablecontactsadd constraintcontacts_firm_id_foreignforeign key (firm_id) referencesfirms(id) on delete cascade)

[iurymacks2]

@Behinder Try to create first : public function up() { Schema::create('firms', function (Blueprint $table) { $table->BigIncrements('id'); $table->string('name'); $table->string('abbreviation')->nullable(); $table->string('address'); $table->string('country')->default('USA'); $table->string('webpage'); $table->timestamps(); }); } and after :

public function up() { Schema::create('contacts', function (Blueprint $table) { $table->BigIncrements('id'); $table->string('name'); $table->string('surname'); $table->string('email'); $table->string('title')->nullable(); $table->text('note')->nullable(); $table->string('country')->nullable(); $table->string('phone')->nullable(); $table->timestamps(); $table->unsignedBigInteger('firm_id'); });

Schema::table('contacts', function($table) { $table->foreign('firm_id')->references('id')->on('firms')->onDelete('cascade'); });

}

[Behinder]

So the order of migrations matters? Don't recal seeing that in documentation.

On Sat, Mar 21, 2020 at 12:21 AM iurymacks2 notifications@github.com wrote:

@Behinder https://github.com/Behinder Try to create first : public function up() { Schema::create('firms', function (Blueprint $table) { $table->BigIncrements('id'); $table->string('name'); $table->string('abbreviation')->nullable(); $table->string('address'); $table->string('country')->default('USA'); $table->string('webpage'); $table->timestamps(); }); } and after :

public function up() { Schema::create('contacts', function (Blueprint $table) { $table->BigIncrements('id'); $table->string('name'); $table->string('surname'); $table->string('email'); $table->string('title')->nullable(); $table->text('note')->nullable(); $table->string('country')->nullable(); $table->string('phone')->nullable(); $table->timestamps(); $table->unsignedBigInteger('firm_id'); });

Schema::table('contacts', function($table) {

$table->foreign('firm_id')->references('id')->on('firms')->onDelete('cascade'); });

}

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/laravel/framework/issues/27717#issuecomment-601950915, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEQPFUMP6Y7MLMVTWVWO33RIP3APANCNFSM4G24NVPA .

[ziyed]

I have changed bigInteger to unsignedBigInteger on foreign key deceleration and it solved.

Schema::create('contacts', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name', 255)->nullable();
    $table->timestamps();            
});

On foreign key deceleration as below

Schema::create('recipients', function (Blueprint $table) {
   $table->bigIncrements('id');
   $table->unsignedBigInteger('contact_id');                 
   $table->timestamps();            
});

Schema::table('recipients', function($table) {
    $table->foreign('contact_id')->references('id')->on('contacts');
});

[Behinder]

Still not working. I got error "SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'contacts' already exists" how can exist if I run migration from scratch? Think I give up and do database without relations

On Tue, Mar 24, 2020 at 6:46 AM Md. Ziyed Uddin notifications@github.com wrote:

I have changed bigInteger to unsignedBigInteger on foreign key deceleration and it solved.

Schema::create('contacts', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('name', 255)->nullable(); $table->timestamps(); });

On foreign key deceleration as below

Schema::create('recipients', function (Blueprint $table) { $table->bigIncrements('id'); $table->unsignedBigInteger('contact_id'); $table->timestamps(); });

Schema::table('recipients', function($table) { $table->foreign('contact_id')->references('id')->on('contacts'); });

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/laravel/framework/issues/27717#issuecomment-603029586, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEQPFUUVXVR7BNNTVHMCELRJBCJPANCNFSM4G24NVPA .

[jackwander]

Still not working. I got error "SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'contacts' already exists" how can exist if I run migration from scratch? Think I give up and do database without relations … On Tue, Mar 24, 2020 at 6:46 AM Md. Ziyed Uddin @.**> wrote: I have changed bigInteger to unsignedBigInteger* on foreign key deceleration and it solved. Schema::create('contacts', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('name', 255)->nullable(); $table->timestamps(); }); On foreign key deceleration as below Schema::create('recipients', function (Blueprint $table) { $table->bigIncrements('id'); $table->unsignedBigInteger('contact_id'); $table->timestamps(); }); Schema::table('recipients', function($table) { $table->foreign('contact_id')->references('id')->on('contacts'); }); — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#27717 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEQPFUUVXVR7BNNTVHMCELRJBCJPANCNFSM4G24NVPA .

run php artisan migrate:fresh

[driesvints]

Hey everyone,

I'm locking this issue because it either has gone off-topic, become a dumping ground for things which shouldn't be in an issue tracker or is just too old. Please try to discuss things further on one of the below channels:

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC