Foreign key constraint '...' are incompatible

Hi,

I'm trying to get Laravel Forge up and running, but it keep posting this error below. Tried to make foreign key and ID to be the same type (integer), but still getting the error.

What am I doing wrong here?

Migrating: 2019_08_03_164948_misc_wormgear

   Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 3780 Referencing column 'misc_id' and referenced column 'id' in foreign key constraint 'misc_wormgear_misc_id_foreign' are incompatible. (SQL: alter table `misc_wormgear` add constraint `misc_wormgear_misc_id_foreign` foreign key (`misc_id`) references `miscs` (`id`) on delete cascade)

    public function up()
    {
        Schema::create('misc_wormgear', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('misc_id')->unsigned();
            $table->integer('wormgear_id')->unsigned();
            $table->foreign('misc_id')->references('id')->on('miscs')
                ->onDelete('cascade');
            $table->foreign('wormgear_id')->references('id')->on('wormgears')
                ->onDelete('cascade');
        });
    }

    public function up()
    {
        Schema::create('wormgears', function (Blueprint $table) {
            $table->integer('id');
            ...
            $table->timestamps();
        });
	...

    public function up()
    {
        Schema::create('miscs', function (Blueprint $table) {
            $table->integer('id');
	    ...
            $table->timestamps();
        });
        
        # For mysql
        DB::statement("ALTER TABLE miscs AUTO_INCREMENT = 311000");
        
    }

Using version: 6.18

Tray2

6 years ago

Best Answer

Level 73

Laravel uses bigInteger as type for the increments and so does foreign so stick to those types for all your primary keys and foreign keys.

3 likes

6 years ago

Level 2

@tray2 That worked! Thanks :)

Just for future reference:

    public function up()
    {
        Schema::create('misc_wormgear', function (Blueprint $table) {
            $table->increments('id');
            $table->bigInteger('misc_id')->unsigned();
            $table->bigInteger('wormgear_id')->unsigned();
            $table->foreign('misc_id')->references('id')->on('miscs')
                ->onDelete('cascade');
            $table->foreign('wormgear_id')->references('id')->on('wormgears')
                ->onDelete('cascade');
        });
    }

    public function up()
    {
        Schema::create('miscs', function (Blueprint $table) {
            $table->bigIncrements('id');
            ...
            $table->timestamps();
        });
        
        # For mysql
        DB::statement("ALTER TABLE miscs AUTO_INCREMENT = 311000");
        
    }

    public function up()
    {
        Schema::create('wormgears', function (Blueprint $table) {
            $table->bigIncrements('id');
            ...
            $table->timestamps();
        });
        
        # For mysql
        DB::statement("ALTER TABLE wormgears AUTO_INCREMENT = 211000");
        
    }

bradycharron

5 years ago

Level 1

I recently ran into this problem during a migration to create a table with a foreign key constraint. I found that it had to due with the collation used on the table. Our DB schema is loaded with a schema file, and that has the table collation set to utf8mb4_0900_ai_ci. It looks like Laravel uses utf8mb4_unicode_ci by default.

I resolved the problem by setting the collation for the table during creation with $table->collation = 'YOUR_COLLATION_VALUE'

1 like

Please or to participate in this conversation.