Can't create foreign key with laravel database migration

Question

I have a problem with my Laravel migrations :(

when i'm running php artisan migrate, it stops on a foreign key.

first migration

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::enableForeignKeyConstraints();
    Schema::create('fichefrais', function (Blueprint $table) {
        $table->char('idVisiteur', 4);
        $table->foreign('idVisiteur')->references('id')->on('visiteur');
        $table->char('mois',6);
        $table->primary(['idVisiteur', 'mois']);
        $table->integer('nbJustificatifs');
        $table->decimal('montantValide', 10, 2);
        $table->date('dateModif');
        $table->char('idEtat', 2);
        $table->foreign('idEtat')->references('id')->on('etat');
    });
}

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

and the second

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::enableForeignKeyConstraints();
    Schema::create('lignefraishorsforfait', function (Blueprint $table) {
        $table->integer('id');
        $table->primary('id');
        $table->char('idVisiteur', 4);
        $table->char('mois',6);
        $table->foreign('idVisiteur')->references('idVisiteur')->on('fichefrais');
        $table->foreign('mois')->references('mois')->on('fichefrais');
        $table->char('libelle', 100);
        $table->date('date');
        $table->decimal('montant', 10, 2);
    });
}

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

After running the command, I got this error :

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table gsb_larave.#sql-176_b9 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table lignefraishors forfait add constraint lignefraishorsforfait_mois_foreign foreign key (mois) references fichefrais (mois) on delete cascade on update cascade)

[PDOException]
SQLSTATE[HY000]: General error: 1005 Can't create table gsb_laravel.#sql-176_b9 (errno: 150 "Foreign key constraint is incorrectly formed")

Answer 1

Does your visiteur table has an id as a primary key? If yes, does it have a data type has char and a length of 4. The idVisiteur in ** lignefraishorsforfait** table has a data type of char which must be the same as the primary key in the visiteur table.

Answer 2

The problem is that the foreign key you are declaring doesn't refer to a primary key. If you want to create a foreign key to a non-primary key, the column 'mois' in your 'fichefrais' table must be a column with a unique constraint on it

Answer 3

First of all your foreign keys must refer to a primary key (which most of the time is 'id' column).

Also you should create the tables in this order:

1- 'etat' table(which i dont see bcs you did not posted the migration code,),

2- 'fichefrais' table,

3- 'lignefraishorsforfait' table. Just change the dates on database/migrations like this (example):

    2020_09_11_150331_create_etat_table.php   
    2020_09_12_000000_create_fichefrais_table.php
    2020_09_13_000000_create_lignefraishorsforfait_table.php

You cannot create first the table which contains foreign key because will not have a key in which to refer.

Answer 4

Use this migrations:

First migration (fichefrais):

public function up()
{
    Schema::create('fichefrais', function (Blueprint $table) {
        $table->integer('idVisiteur')->unsigned()->nullable();
        $table->foreign('idVisiteur')->references('id')->on('visiteur');
        $table->char('mois',6);
        $table->primary(['idVisiteur', 'mois']);
        $table->integer('nbJustificatifs');
        $table->decimal('montantValide', 10, 2);
        $table->date('dateModif');
        $table->integer('idEtat')->unsigned()->nullable();
        $table->foreign('idEtat')->references('id')->on('etat');
    });
}

Second migration (lignefraishorsforfait):

public function up()
{
    Schema::create('lignefraishorsforfait', function (Blueprint $table) {
        $table->integer('id');
        $table->primary('id');
        $table->integer('mois')->unsigned()->nullable();
        $table->foreign('mois')->references('mois')->on('fichefrais');
        $table->integer('idVisiteur')->unsigned()->nullable();
        $table->foreign('idVisiteur')->references('idVisiteur')->on('fichefrais');
        $table->char('libelle', 100);
        $table->date('date');
        $table->decimal('montant', 10, 2);
    });
}

Answer 5

You are using MySQL, so Are your tables defined in InnoDB engine? MyISAM doesn't accept foreign key...