Sometimes when running a Laravel Migration you get the error: errno 150 "Foreign key constraint is incorrectly formed". This error is mainly caused because your foreign key column is declared in a different data type that is the related key column on source table.
Main cause of the error “errno 150: Foreign key constraint is incorrectly formed”
The most common cause to the error is that the foreign key column is created with the wrong type, related to referenced primary column.
Since increments() creates an unsigned integer column, you need to define the foreign key column as unsigned integer too.
Default migrations in Laravel 6+ use bigIncrements(), so you need to use unsignedBigInteger() method:
$table->unsignedBigInteger('order_id');
For default migrations in older versions of Laravel use unsignedInteger() method:
$table->unsignedInteger('order_id');
Or:
$table->integer('order_id')->unsigned();
See a migration example with error:
Schema::create('books', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->timestamps();
$table->softDeletes();
});
Schema::create('orders', function (Blueprint $table) {
$table->increments('id');
$table->integer('book_id');
$table->timestamps();
$table->softDeletes();
$table->foreign('book_id')->references('id')->on('books');
});
}
So to solve an error like:
[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table orders.#sql-b5b_b2a (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table orders add constraint orders_book_id_foreign foreign key (book_id) references books (id))
[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000]: General error: 1005 Can't create table books.#sql-b5b_b2a (errno: 150 "Foreign key constraint is incorrectly formed")
You should change the line that creates the book_id column:
Schema::create('books', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->timestamps();
$table->softDeletes();
});
Schema::create('orders', function (Blueprint $table) {
$table->increments('id');
$table->unsignedBigInteger('book_id'); // change this line
$table->timestamps();
$table->softDeletes();
$table->foreign('book_id')->references('id')->on('books');
});
}
Other causes for the error “errno 150: Foreign key constraint is incorrectly formed”
Wrong referenced table name
One of the most common sins of developers, misname variables, tables, fields, etc. So yes, a simple and innocent mistake like that can bring the error message: “errno 150 Foreign key constraint is incorrectly formed on Laravel Migration”. Just correct the table in the foreign key creation statement:
Schema::create('books', function (Blueprint $table) {
$table->increments('id');
...
});
Schema::create('orders', function (Blueprint $table) {
$table->increments('id');
$table->unsignedBigInteger('book_id');
$table->foreign('book_id')->references('id')->on('book'); // correct name for books
});
}
Migration files order
Sometimes the reason for this error is due to the order of which the migration files are listed or error due to type casting.
Always make sure that the migration of the file which the foreign constraints is to be imposed on comes after the parent migration. And for the latter, make sure its an unsignedBigInteger, although former version of laravel (<5.4) could ignore this type casting error.
So, migration files should be created in such a way that the parent migration should come first and the migration file with the foreign key next.
Also, the foreign key and the primary id in the other table should have exactly similar property. If the primary id is increments then make the foreign key integer('xxx_id')->unsigned();
MySQL MyISAM
Check your MySQL table’s engine. In some cases, you may be referencing a MyISAM table in an InnoDB source table. After changing the reference table engine to InnoDB, it should work!
This can be caused when tables created before the migration where MyISAM from an legacy system and the migrated are innoDB by default, so the mix of table types were an issue in my case.
Conclusions
Pay attention when creating foreign keys on Laravel Migration to create the foreign columns with the same data type of the original columns, also creating the migration files in correct order.
Comments