CODEWITHSUNDEEP
phpmysqllaraveldatabase-migrationlaravel-migrations
user9507446
user9507446

Reputation: 401

Laravel throws errno: 150 "Foreign key constraint is incorrectly formed" despite the correct syntax

I am trying to add a table containing two foreign keys, see below:

Schema::create('semester_cohorts', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('semester_id');
        $table->unsignedBigInteger('cohort_id');
        $table->timestamps();

        $table->foreign('semester_id')
            ->references('semesters')
            ->on('id')
            ->onDelete('cascade');

        $table->foreign('cohort_id')
            ->references('id')
            ->on('cohorts')
            ->onDelete('cascade');
    });

I get the following message: PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table [table name](errno: 150 "Foreign key constraint is incorrectly formed")"), even though the respective referenced tables exist in the database, there are no typos in names and the types of primary/foreign keys match. What could cause this problem? `

Upvotes: 0

Views: 52

Answers (3)

Makdous
Makdous

Reputation: 1433

I actually had this issue and its because of the type unsignedBigInteger.

Foreign key column must be the same data type as the primary key it references.

So if your primary key in semesters & cohorts table is of type bigIncrements

you should define the Foreign key type as BigInteger then follow it with an unsigned():

  $table->bigIncrements('id');
    $table->bigInteger('semester_id')->unsigned();
    $table->bigInteger('cohort_id')->unsigned();
    $table->timestamps();

Edit:

Like kerbholz said you have an error in defining the foreign keys

Upvotes: 0

mcklayin
mcklayin

Reputation: 1360

There are some problems possible here:

  • Check column types for both tables, types should be the same

  • Try to split migrations. Firstly create table semester_cohorts then below use

    Schema::table('semester_cohorts', function (Blueprint $table) {
   $table->foreign('semester_id')
        ->references('id')
        ->on('semesters')
        ->onDelete('cascade');

    $table->foreign('cohort_id')
        ->references('id')
        ->on('cohorts')
        ->onDelete('cascade');
});

Upvotes: 1

user8034901
user8034901

Reputation:

You got references and on mixed up in your first foreign key:

$table->foreign('semester_id')
            ->references('semesters')
            ->on('id')
            ->onDelete('cascade');

should be

$table->foreign('semester_id')
            ->references('id')
            ->on('semesters')
            ->onDelete('cascade');

Upvotes: 4

Related Questions