Laravel Migration External key constraint malformed

Question

When migrating my database, this error appears, below my code is accompanied by the error that I get when I try to migrate.

the code

public function up() { Schema::create('meals', function (Blueprint $table) { $table->increments('id'); $table->integer('user_id')->unsigned(); $table->integer('category_id')->unsigned(); $table->string('title'); $table->string('body'); $table->string('meal_av'); $table->timestamps(); $table->foreign('user_id') ->references('id') ->on('users') ->onDelete('cascade'); $table->foreign('category_id') ->references('id') ->on('categories') ->onDelete('cascade'); }); } 

Error message

 [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table `meal`.`#sql-11d2_1 4` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `meals` add constraint meals_category_id_foreign foreign key (`catego ry_id`) references `categories` (`id`) on delete cascade) 

Answer 1

@JuanBonnett Your question inspired me to answer, I accepted it at laravel to automate the process, not taking into account the time of creation of the file itself. According to the workflow, food will be created in front of the table (categories), because I created a schematic file (food) in front of the categories. that was my fault.

Answer 2

When creating a new table in Laravel. The migration will be generated as follows:

 $table->bigIncrements('id'); 

Instead (in older versions of Laravel):

 $table->increments('id'); 

When using bigIncrements foreign key expects bigInteger instead of an integer . So your code will look like this:

 public function up() { Schema::create('meals', function (Blueprint $table) { $table->increments('id'); $table->unsignedBigInteger('user_id'); //changed this line $table->unsignedBigInteger('category_id'); //changed this line $table->string('title'); $table->string('body'); $table->string('meal_av'); $table->timestamps(); $table->foreign('user_id') ->references('id') ->on('users') ->onDelete('cascade'); $table->foreign('category_id') ->references('id') ->on('categories') ->onDelete('cascade'); }); } 

You can also use increments instead of bigIncrements as Kiko Sejo said.

The difference between Integer and BigInteger is the size of:

• int => 32-bit
• bigint => 64-bit

Answer 3

just add ->unsigned()->index() to the end of the foreign key and it will work

Answer 4

Everything was in the right order for me, but it still didn't work. Then I found out that the primary key must be an unsigned one.

 //this didn't work $table->integer('id')->unique(); $table->primary('id'); //this worked $table->integer('id')->unsigned()->unique(); $table->primary('id'); //this worked $table->increments('id'); 

Answer 5

In my case, the new Laravel convention caused this error.

By simply exchanging the id create the table, everything worked out.

 $table->increments('id'); // ok 

instead:

 $table->bigIncrements('id'); // was the error. 

Already working with Laravel v5.8 , there was no such error before.

Answer 6

Migrations must be created from top to bottom.

First create a migration for tables that do not belong to anyone.

Then create migrations for tables belonging to the previous one.

---

A simplified answer to the problem with the movement of the table:

To set the storage engine for a table, set the engine property in the schema builder:

 Schema::create('users', function ($table) { $table->engine = 'InnoDB'; $table->increments('id'); }); 

From Laravel Docs: https://laravel.com/docs/5.2/migrations

Answer 7

if you use ->onDelete('set null') in the foreign key definition, make sure that the foreign key field is nullable() i.e.

 //Column definition $table->integer('user_id')->unsigned()->index()->nullable(); //index() is optional //... //... //Foreign key $table->foreign('user_id') ->references('id') ->on('users') ->onDelete('set null'); 

Answer 8

You must create your migration in order, for example, I want my users have a role_id field that is in my roles table

I will start my migration of php artisan make:migration create_roles_table --create=roles role first php artisan make:migration create_roles_table --create=roles

then my second user migration php artisan make:migration create_users_table --create=users

php artisan migration will be executed using the order of the generated files 2017_08_22_074128 _create_roles_table.php and 2017_08_22_134306 _create_users_table to check the datetime order, which will be the order of execution.

files 2017_08_22_074128_create_roles_table.php

 public function up() { Schema::create('roles', function (Blueprint $table) { $table->increments('id'); $table->string('name', 50); $table->timestamps(); }); } 

2017_08_22_134306_create_users_table

 public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->integer('role_id')->unsigned(); $table->string('name'); $table->string('phone', 20)->unique(); $table->string('password'); $table->rememberToken(); $table->boolean('active'); $table->timestamps(); $table->foreign('role_id')->references('id')->on('roles'); }); } 

Answer 9

In my case, the problem was that one of the reference tables was InnoDB and the other was MyISAM .

MyISAM does not support foreign key relationships.

So now both are InnoDB tables . The problem is resolved.

Answer 10

Maybe this can help anyone who landed here: I just experienced the same problem, and in my case it was that I had a (composite) unique constraint set in the foreign key column BEFORE the foreign key. I solved the problem by setting a “unique” statement AFTER the “STATEMENT”.

Working:

 $table->foreign('step_id')->references('id')->on('steps')->onDelete('cascade'); $table->unique(['step_id','lang']); 

Does not work:

 $table->unique(['step_id','lang']); $table->foreign('step_id')->references('id')->on('steps')->onDelete('cascade'); 

Answer 11

I got the same message for a data type problem.

I used bigIncrements () for 'id', and when I used it as a foreign key (used bigInteger () ), I got an error.

I found a solution, bigIncrements () returns unsignedBigInteger . Therefore, you must use unsignedBigInteger () instead of bigInteger () in an external key

Share it because it can help others.

Answer 12

Add -> nullable () to the field and make sure that all the fields you are accessing do exist.

Answer 13

The order in which the migration files are created must be sorted, and the foreign key must have exactly the same property as the primary key in another table.

Answer 14

Remember that it is important that the link fields and the link fields have the same data type.

Answer 15

You must first create a table of categories and users when creating the “dishes”.

To solve this problem, you must rename the category and user migration files before the date preceding the food migration file, which creates them in front of the food table.

---

sample:

 2019_04_10_050958_create_users_table 2019_04_10_051958_create_categories_table 2019_04_10_052958_create_meals_table