1215 Cannot add foreign key constraint in laravel 5, foreign key

Question

2019_04_23_164151_create_contacts_table.php migration file

<?php

 use Illuminate\Support\Facades\Schema;
 use Illuminate\Database\Schema\Blueprint;
 use Illuminate\Database\Migrations\Migration;

 class CreateContactsTable extends Migration
 {
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{

    Schema::create('contacts', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('surname');
        $table->timestamps();
    });

    Schema::create('contacts_relations', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedInteger('phone_id')->nullable()->default(1);
        $table->unsignedInteger('contact_id')->nullable()->default(1);
    });

    Schema::create('contact_phone', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->integer('phone_number');
        $table->unsignedInteger('contacts_relations_id')->nullable()->default(1);
    });

}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::dropIfExists('contacts');
}}

2019_04_24_183110_contacts_relations.php foreign key setter

 <?php

 use Illuminate\Support\Facades\Schema;
 use Illuminate\Database\Schema\Blueprint;
 use Illuminate\Database\Migrations\Migration;

 class ContactsRelations extends Migration
 {
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::table('contacts_relations', function (Blueprint $table) {
        $table->foreign('contact_id')->references('id')->on('contacts');
    });
    Schema::table('contact_phone', function (Blueprint $table) {
        $table->foreign('contacts_relations_id')->references('id')->on('contacts_relations');
    });

}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    //
}
}

run php artisan migrate: fresh and get SQLSTATE [HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table contacts_relations add constraintcontacts_relations_contact_id_foreign foreign key (contact_id) referencescontacts (id ) on delete cascade on update cascade)

Marcin Nabiałek Marcin Nabiałek · Accepted Answer · 2019-04-23T19:00:46

You should make sure all columns are eact same type. For example in table contacts the id column is unsigned big integer, so when you create contact_id column in contacts_relations instead of:

$table->unsignedInteger('phone_id')->nullable()->default(1);

it should be:

$table->unsignedBigInteger('phone_id')->nullable()->default(1);

Similar thing for contacts_relations_id column

1215 Cannot add foreign key constraint in laravel 5, foreign key

1 Answers