laravel errno 150 foreign key constraint is incorrectly formed

Can anybody help me to solve this problem?

There are 3 tables with 2 foreign keys:

         Schema::create('users', function (Blueprint $table) {
                    $table->increments('id');
                    $table->string('name');
                    $table->string('email')->unique();
                    $table->string('password');
                    $table->rememberToken();
                    $table->timestamps();
                });

        Schema::create('firms', function (Blueprint $table) {
                    $table->increments('id');
                    $table->string('title')->nullable();  
                    $table->integer('user_id')->unsigned()->nullable();
                    $table->foreign('user_id')->references('id')->on('users');
                    $table->timestamps();
                });
       Schema::create('jobs', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title')->nullable();
            $table->integer('firm_id')->unsigned()->nullable();
            $table->foreign('firm_id')->references('id')->on('firms');
            $table->timestamps();
        });

Error after running migration:

[Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `job`.`#sql-5fc_a1`
   (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter ta
  ble `firms` add constraint `firms_user_id_foreign` foreign key (`user_id`)
  references `users` (`id`))

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

In case of foreign keys, the referenced and referencing fields must have exactly the same data type.

You create the id fields in both users and firms as signed integers. However, you create both foreign keys as unsigned integers, therefore the creation of the keys fail.

You need to either add the unsigned clause to the id field definitions, or remove the unsigned clause from the foreign key fields.

If you face errno 150 "Foreign key constraint is incorrectly formed" then go to your migrations folder and check whether the parent table migration was executed first or not. If child table migration is executed first according to the migration order, it will try to find foreign key binding and shows that error.

New version 5.1 Laravel! You practice and you know PHP create sites I propose today to discover all the Laravel PHP framework. 150 "Foreign key constraint is

Most of the time this kind of error occurs due to the datatype mismatch on both the table.

Both primary key table & foreign key table should use same datatype and same option.

For example:

users

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

orders

 Schema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products');
            $table->bigInteger('user_id')->unsigned();
           $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

            $table->timestamp('added_on');
        });

On above example, I am trying to assign foreign key to users table from order table but I have bigInteger datatable in order table while in user table, I have simple integer. That's why it's generated this kind of error.

Also, If you have used unsigned(), nullable() etc options with primary or foreign key then you should use same at both the place.

In Laravel 5.8, sometime when you generate a create table migration with a foreign key constraints, running migration may encounter 150 “Foreign key constraint is incorrectly formed” error. This is mainly happens when you take foreign key column of integer type instead of bigInteger, and this new laravel convention was causing this error.

For PHP laravel 5.8 use unsigned modifier in this format

$table->unsignedBigInteger('user_id');

drop all tables in the database and run the migration again

errno: 150 "Foreign key constraint is incorrectly formed" Posted 3 years ago by sanjivsharma I have many to one relation from Category to Post, and following are from respective migrations:

n’t create table `dbstudent`.`#sql-11c8_1f` (errno: 150 “Foreign key constraint is incorrectly formed”) (SQL: alter table `transactions` add constraint `transac tions_transaction_type_id_foreign` foreign key (`transaction_type_id`) reference s `transactions_types` (`id`))

"Foreign key constraint is incorrectly formed". I tried a few things that other questions' answers had mentioned (that I may have misunderstood), such as making user.id unique as well, though that did not work.

Introduction. A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

SQLSTATE[HY000]: General error: 1005 Can't create table `dbname`.`#sql-1ee8_1f4` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `stocks` add constraint `stocks_medicine_id_foreign` foreign key (`medicine_id`) references `medicines` (`id`) on delete restrict)

Comments
  • Sorry, it's not working: Schema::create('firms', function (Blueprint $table) { $table->increments('id')->unsigned(); Schema::create('jobs', function (Blueprint $table) { $table->increments('id'); $table->integer('firm_id')->unsigned(); $table->foreign('firm_id')->references('id')->on('firms');
  • and this is not working too: Schema::create('firms', function (Blueprint $table) { $table->increments('id'); Schema::create('jobs', function (Blueprint $table) { $table->increments('id'); $table->integer('firm_id'); $table->foreign('firm_id')->references('id')->on('firms');
  • great, it helped me.
  • Forget about every other thing said this is important the referenced and referencing fields must have exactly the same data type. I was nearly going to hang myself. Thanks mate.
  • Thank you for your thorough explanations. The first bullet point of number 3 worked for me!
  • Unique : the referenced column must be unique - this is not necessarily true in MySQL (remember, the error message got nothing to do with laravel, it comes from MySQL). This requirement used to be true for NDB table type, v8.0 aligned NDB requirement with innodb's. For the widely used innodb table type the requirement is that the referenced column must be indexed.
  • If you want a more comprehensive list of possible causes, then see the following SO question: stackoverflow.com/questions/1457305/…
  • Thank you so much. #3 Worked like a charm
  • Order was my mistake. Thanks .
  • Can you explain how this answers the question?
  • As you can see "users" table was on the top, and cashier has "foreign key" that refers to "users" table same with student. Just keep your table organized when creating migration.