CODEWITHSUNDEEP
phpmysqllaravelmigration
m4rii0
m4rii0

Reputation: 37

General error: 1215 Cannot add foreign key constraint in laravel

Doing the migrations I got this error: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table books add constraint books_writer_id_foreign foreign key (writer_id) references writers (id)) I've tried a lot of things but noone looks to work.

2018_02_18_3165165_create_books_table.php

<?php

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

class CreateBooksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            $table->increments('id');


            $table->string('name');
            $table->text('description');

            $table->integer('numPages');
            $table->enum('language', ['spanish', 'english']);

            $table->date('wrote_date')->nullable();


            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
        });
        Schema::table('books', function (Blueprint $table) {
            $table->integer('writer_id')->unsigned();
            $table->foreign('writer_id')->references('id')->on('writers');
        });
    }

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

2018_02_18_192915_create_writers_table 

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

class CreateWritersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('writers', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->text('description');

            $table->string('nationality');
            $table->date('year_date')->nullable();
            $table->date('dead_date')->nullable();

            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
        });
    }

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

Edit: The error was because the first migration was the books, and then the writers causing that the error.

Upvotes: 3

Views: 10904

Answers (4)

saber tabatabaee yazdi
saber tabatabaee yazdi

Reputation: 4959

change in migration source your_table_name

Schema::create('your_table_name', function (Blueprint $table) {
$table->BigIncrements('id');

to

Schema::create('your_table_name', function (Blueprint $table) {
$table->increments('id');

Upvotes: 1

AH.Pooladvand
AH.Pooladvand

Reputation: 2069

Sometimes based on my experience too close timestamps will break the code and will throw an exception because program thinks that writers table is created after books table try to change writers_table timestamp something like:
2018_02_16_31615

Upvotes: 7

Alexey Mezenin
Alexey Mezenin

Reputation: 163968

Move this line to the first closure:

$table->integer('writer_id')->unsigned();

Put it right after:

$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

Also, you need to add timestamps to both migration files so Laravel first create the writers table and then the books table:

2018_02_01_000000_create_writers_table.php
2018_02_02_000000_create_books_table.php

Upvotes: 0

Mahdi Younesi
Mahdi Younesi

Reputation: 7509

Make it unsignedInteger because you're using increments()

$table->unsignedInteger('writer_id')->nullable();

Upvotes: 5

Related Questions