laravelforeign-keysforum

Laravel - 5.4v and 5.8v differences in foreign key


What are differences in foreign key for Laravel 5.4 version and Laravel 5.8 version? I tried to install devdojo/chatter on my 5.8 version Laravel project and at migrating it shows me all kind of errors for foreign key, but when I try it on 5.4 version it works pretty well, no errors at all. Please help, I can't find solution and I need it in my project. Thanks!

This is my error: SQLSTATE[HY000]: General error: 1005 Can't create table `cdm`.`chatter_discussion` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `chatter_discussion` add constraint `chatter_discussion_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade on update cascade)

This is my chatter_discussion table:

<?php

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

class CreateChatterDiscussionTable extends Migration
{
    public function up()
    {
        Schema::create('chatter_discussion', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('chatter_category_id')->unsigned()->default('1');
            $table->string('title');
            $table->integer('user_id')->unsigned();
            $table->boolean('sticky')->default(false);
            $table->integer('views')->unsigned()->default('0');
            $table->boolean('answered')->default(0);
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('chatter_discussion');
    }
}

And this is foreign_keys table:

<?php

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

class CreateForeignKeys extends Migration
{
    public function up()
    {
        Schema::table('chatter_discussion', function (Blueprint $table) {
            $table->foreign('chatter_category_id')->references('id')->on('chatter_categories')
                        ->onDelete('cascade')
                        ->onUpdate('cascade');
            $table->foreign('user_id')->references('id')->on('users')
                        ->onDelete('cascade')
                        ->onUpdate('cascade');
        });
        Schema::table('chatter_post', function (Blueprint $table) {
            $table->foreign('chatter_discussion_id')->references('id')->on('chatter_discussion')
                        ->onDelete('cascade')
                        ->onUpdate('cascade');
            $table->foreign('user_id')->references('id')->on('users')
                        ->onDelete('cascade')
                        ->onUpdate('cascade');
        });
    }

    public function down()
    {
        Schema::table('chatter_discussion', function (Blueprint $table) {
            $table->dropForeign('chatter_discussion_chatter_category_id_foreign');
            $table->dropForeign('chatter_discussion_user_id_foreign');
        });
        Schema::table('chatter_post', function (Blueprint $table) {
            $table->dropForeign('chatter_post_chatter_discussion_id_foreign');
            $table->dropForeign('chatter_post_user_id_foreign');
        });
    }
}

Solution

  • On Laravel 5.4, all id columns are type increments by default but On Laravel 5.8 it is bigIncrements by default. So change user_id column type to bigInteger on chatter_discussion table will fix your problem:

    $table->bigInteger('user_id')->unsigned();
    

    Remember that your local key and foreign key should always have the same type on your migrations.