SQLSTATE [HY000]: General Error: 1215 Unable to add Laravel foreign key constraint
I am trying to create foreign keys using artisan
but this error appears.
[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 lot table i use lot_id
as id
it simulates Lot.php i add:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Lot extends Model {
protected $primaryKey = 'lot_id';
}
Any idea how I can resolve this error?
source to share
Apply the following rules to your migration files:
[1]
The parent pivot table should be based on mechanisms that support foreign key references (like InnoDB for mysql).
Do $table->engine = "InnoDB";
migrations in your file, right before the other column definitions.
I observe that laravel is always MyISAM by default, so this line is required.
[2]
The specified columns in the parent must be primary or unique key (s).
These declarations in the parent table are fine:
$table->increments("id");
means that the "id" column is referencing.
$table->column_type("column_name")->unique();
means column "column_name" is a reference
[3]
The pivot table's table column must be of the same type as its referenced column of the parent table.
So, for example, a pivot table column that should refer to increments ("ID") should be of type unsignedInteger.
If the parent table is of type char (20), then the column of the table columns used for the reference must also be of type char (20).
After doing all three of the above, define the appropriate foreign key relationship.
source to share
Quoting this answer:
To find a specific error, do the following:
SHOW ENGINE INNODB STATUS;
And look in the section
LATEST FOREIGN KEY ERROR
.
It could be a type problem. comment_lot_id
must be of the same type as lot_id
. Perhaps one of them is signed and the other unsigned.
source to share
It looks like this was not a problem for you, but I ran into the same bug in Laravel 5.8 and found an interesting problem: Laravel now defaults to "bigIncrements" in the "id" column, not just "increments". Thus, instead of referring to it with an integer value like before, you should refer to it using bigInteger.
If your parent table looks like this:
$table->bigIncrements('id');
Then the child migration should look like this:
$table->bigInteger('parent_id')->unsigned()->index();
$table->foreign('parent_id')->references('id')->on('parent');
Hope this helps anyone who ran into this problem in version 5.8 and up.
source to share