[SOLVED] General error : 1005 Can't create table ... (errno:150 "Foreign key constraint is incorrectly formed"

General error : 1005 Can't create table ... (errno:150 "Foreign key constraint is incorrectly formed"

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!!!

Solution