General error : 1005 Can't create table ... (errno:150 "Foreign key constraint is incorrec-CodePudding

I have to run migrations on my online project but it doesn't work for a foreign key. I have two tables : media and video_categorie (existing) Here is my migration file to create the video_categorie file:

<?php

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

class CreateVideoCategorieTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('video_categorie', function (Blueprint $table) {
            $table->increments('id');
            $table->string('nom_fr', 50);
            $table->string('nom_en', 50)->nullable();
            $table->unsignedSmallInteger('ordre')->nullable();
            $table->timestamps();
        });
    }

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

And the other to create the foreign_key on my media table :

<?php

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

class AddForeignKeyToMediaTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('media', function (Blueprint $table) {
            $table->unsignedInteger('video_categorie_id')->nullable();
            $table->unsignedSmallInteger('ordre_video')->nullable();
            $table->foreign('video_categorie_id')->references('id')->on('video_categorie');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('media', function (Blueprint $table) {
            $table->dropForeign('media_video_categorie_id_foreign');
            $table->dropColumn('video_categorie_id');
            $table->dropColumn('ordre_video');
        });
    }
}

When I tried on my local server it works like a charm, my database was updated like I wanted. But on my web hosting I have this error wheen I try to run

php artisan migrate

Migrating: 2022_09_15_092133_create_video_categorie_table
Migrated:  2022_09_15_092133_create_video_categorie_table (7.91ms)
Migrating: 2022_09_15_115815_add_foreign_key_to_media_table

   Illuminate\Database\QueryException

  SQLSTATE[HY000]: General error: 1005 Can't create table `stag_db`.`media` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `media` add constraint `media_video_categorie_id_foreign` foreign key (`video_categorie_id`) references `video_categorie` (`id`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

       9 vendor frames
  10  database/migrations/2022_09_15_115815_add_foreign_key_to_media_table.php:20
      Illuminate\Support\Facades\Facade::__callStatic("table")

       22 vendor frames
  33  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

Does anyone know where this is coming from?

I have checked my local and my hosting web version of php and mysql and they are not the same.

Local php : PHP 7.4.3

Hosting php : PHP 7.3.33

Local mysql : mysql Ver 8.0.30

Hosting mysql : mysql Ver 15.1 Distrib 10.3.36-MariaDB

Is there a problem with these differences?

Thanks!

EDIT

Last migrations (php artisan migrate:status):

| Yes  | 2022_08_18_135729_add_fichier_column_to_contact_table                                                | 32    |
| Yes  | 2022_08_29_120103_add_contact_motif_name_column_to_contact_table                                     | 33    |
| Yes  | 2022_09_15_092133_create_video_categorie_table                                                       | 33    |
| No   | 2022_09_15_115815_add_foreign_key_to_media_table                                                     |       |
| No   | 2022_09_15_120150_add_orph_video_column_to_media_table                                               |       |
 ------ ------------------------------------------------------------------------------------------------------ -------

EDIT

After many hours I finally found where the problem was! If it can help other people ;)

On my webhosting the default storage engine that is used is MyISAM, so my new table video_categorie was created with this engine.

But this engine doen't allow to establish relations between tables.

My first migration which create the table video_categorie works but when I try on my second migration to establish a foreign key, it does'nt work due to the video_categorie engine.

I contact my webhosting to change my server mysql configuration to default engine InnoDB.

While waiting for them to answer me I have just run my first migration, then change manually the table engine(ALTER TABLE video_categorie ENGINE = InnoDB).

Finally I run the second migration and it works!!!

CodePudding user response：

The error is informing you that the column type of your primary key on media and foreign key on video_categorie do not match.

My recommendation would be to change your migrations and make use of the column definition helpers.

CreateVideoCategorieTable

Schema::create('video_categorie', function (Blueprint $table) {
    $table->id('id');  // using the id column type
    $table->string('nom_fr', 50);
    $table->string('nom_en', 50)->nullable();
    $table->unsignedSmallInteger('ordre')->nullable();
    $table->timestamps();
});

AddForeignKeyToMediaTable

Schema::table('media', function (Blueprint $table) {
    $table->foreignId('video_categorie_id')->nullable();
    $table->unsignedSmallInteger('ordre_video')->nullable();
    $table->foreign('video_categorie_id')->references('id')->on('video_categorie');
});

Update

For clarity, the issue is that the column type integer and unsignedInteger are not compatible and so you need to alter your foreign key column type to be compatible with primary key column type. The above shows how that mapping could be done.

This amendement needs to be done in your AddForeignKeyToMediaTable migration, replace the $table->unsignedInteger('video_categorie_id')->nullable(); statement entirely. If it remains in your migrations anywhere, you'll continue to see the error. You can't add a new rollback migration for this statement as it has never executed due to the migrations failing.