Laravel migration Error no: 150 “Foreign key constraint is incorrectly formed”

Question

I have a ‘posts’ table and an ‘arrival’ table which references ‘flightno’ (in text string format) as a foreign key. However, when I run the Laravel migration I get the dreaded error:

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table atc.#sql-2350_84 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table arrival add constraint arrival_flightno_foreign foreign key (flightno) references posts (flightno))

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

Posts

Schema::create('posts', function (Blueprint $table) {
    $table->increments('id');
    $table->string('flightno');
    $table->string('flighttype');
    $table->string('toa');
    $table->string('doa');
    $table->string('runway');
    $table->string('route');
    $table->string('parking');
    $table->timestamps();
}); 

Arrival

Schema::create('arrival', function (Blueprint $table) {
    $table->increments('id');
    $table->string('flightno');
    $table->string('cleaning');
    $table->string('rampservice');
    $table->string('waste');
    $table->string('deicing');
    $table->foreign('flightno')->references('flightno')->on('posts')->onDelete('cascade');
    $table->timestamps();
});

Answer 1

Seems to me that you forgot to put indexes and set length of flightno columns. This should work:

Posts

Schema::create('posts', function (Blueprint $table) {
    // ...
    $table->string('flightno', 30)->index();
    // ...
}); 

Arrival

Schema::create('arrival', function (Blueprint $table) {
    // ...
    $table->string('flightno', 30)->index();
    // ...
}); 

Answer 2

In the foreign key column use unsignedBigInteger to avoid mismatch foreign key data type problem.

For example in your arrival table use the type of flightno as unsignedBigInteger.

Posts:

Schema::create('posts', function (Blueprint $table) {
    $table->increments('id');
    $table->string('flightno');
    $table->string('flighttype');
    $table->string('toa');
    $table->string('doa');
    $table->string('runway');
    $table->string('route');
    $table->string('parking');
    $table->timestamps();
});

Arrival:

Schema::create('arrival', function (Blueprint $table) {
    $table->increments('id');
    //---Change flightno type to unsignedBigInteger and if u faced any problem just use nullable at the end of flightno.
    $table->unsignedBigInteger('flightno');
    $table->string('cleaning');
    $table->string('rampservice');
    $table->string('waste');
    $table->string('deicing');
    $table->foreign('flightno')->references('flightno')->on('posts')->onDelete('cascade');
    $table->timestamps();
});

It will solved the problems of a lot of people that faced this problem.