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

Question

Im trying to create a foreign keys using artisan, but this error show up.

[Illuminate\Database\QueryException]                                                                                                                                                                             
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `comments` add constraint `comments_comment_lot_id_foreign` foreign key (`comment_lot_id`) references `lots` (`lot_id`  
  ) on delete cascade) 

This is my migration:

<?php

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

class CreateCommentsTable extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->increments('id');
            $table->text('comment');
            $table->integer('comment_lot_id')->unsigned();
            $table->timestamps();
        });

        Schema::table('comments', function ($table) {
            $table->foreign('comment_lot_id')->references('lot_id')->on('lots')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropForeign(['comment_lot_id']);
        Schema::dropIfExists('comments');
    }
}

in the lots table i use lot_id as id it model Lot.php i add:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Lot extends Model {
    protected $primaryKey = 'lot_id';

}

Any idea how can i resolve this error?

Answer 1

Apply these rules below to your migration files:

[1]

The parent, pivot table(s) must be based on engines that supports foreign key referencing (e.g InnoDB for mysql).

Do $table->engine = “InnoDB”; in your migration file, right before other column definitions.

I observe laravel always default to MyISAM hence this line is a must.

[2]

The referenced columns in the parent must be a primary or unique key(s).

These declarations in the parent table are fine:

$table->increments(“id”); means column “id” is referencable

$table->column_type(“column_name”)->unique(); means column “column_name” is referencable

[3]

The pivot table column must be of the same type as that of its referenced parent table column.

So for example, the pivot table column that should reference increments(“id”) must of type of unsignedInteger.

If parent table is type char(20), then pivot table column used to reference it must be type char(20) as well.

Having done all three above, define your foreign key relationship as appropriate.

Answer 2

Looks like this was not the problem for you, but I arrived at this same error in Laravel 5.8 and found an interesting issue: Laravel now defaults the 'id' column to 'bigIncrements' instead of just 'increments'. So instead of referencing it with 'integer' like before, you have to reference it with 'bigInteger'.

If your parent table looks like this:

$table->bigIncrements('id');

Then the child migration needs to look like this:

$table->bigInteger('parent_id')->unsigned()->index();
$table->foreign('parent_id')->references('id')->on('parent');

Hopefully this helps anyone else encountering this problem in 5.8 and beyond.

Answer 3

Quoting this answer:

To find the specific error run this:

SHOW ENGINE INNODB STATUS;

And look in the LATEST FOREIGN KEY ERROR section.

It may be a problem of type. comment_lot_id must be the exact same type as lot_id. Maybe one is signed and the other unsigned.