errno: 150 "Foreign key constraint is incorrectly formed while migration

Question

category_id foreign key is not taken while migration and throws some error.

Here are my migration files:

create_categories_table :

public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->integer('parent_id')->nullable();
            $table->timestamps();
        });
    }

create_products_table:

public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('slug');
            $table->integer('price')->nullable();
            $table->integer('category_id');
            $table->text('description')->nullable();
            $table->integer('quantity')->default(0);
            $table->string('image')->nullable();
            $table->json('gallery')->nullable();
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
            $table->timestamps();
        });
    }

I am getting this error in my console.

Illuminate\Database\QueryException

  SQLSTATE[HY000]: General error: 1005 Can't create table `eshop`.`products` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `products` add constraint `products_category_id_foreign` foreign key (`category_id`) references `categories` (`id`) on delete cascade)

  at C:\eshop\vendor\laravel\framework\src\Illuminate\Database\Connection.php:692
    688▕         // If an exception occurs when attempting to run a query, we'll format the error
    689▕         // message to include the bindings with SQL, which will make this exception a
    690▕         // lot more helpful to the developer instead of just the database's errors.
    691▕         catch (Exception $e) {
  ➜ 692▕             throw new QueryException(
    693▕                 $query, $this->prepareBindings($bindings), $e
    694▕             );
    695▕         }
    696▕     }

  1   C:\eshop\vendor\laravel\framework\src\Illuminate\Database\Connection.php:485
      PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `eshop`.`products` (errno: 150 "Foreign key constraint is incorrectly formed")")

  2   C:\eshop\vendor\laravel\framework\src\Illuminate\Database\Connection.php:485
      PDOStatement::execute()

if u r using laravel 7 add `$table->unsignedInteger('category_id');` in your `products` migration — Pradeep, Aug 16 '21 at 15:04
Tried. Same error. I have mentioned my full error in my post. Please check. — Tanjib Rubyat, Aug 16 '21 at 15:07
`increments('id');` and `unsignedInteger` both creates unsigned integer values https://laravel.com/docs/7.x/migrations#creating-columns — Pradeep, Aug 16 '21 at 15:14
Is it conflicting with another project? Because I have copied those codes and pasted it inside both files. — Tanjib Rubyat, Aug 16 '21 at 15:14
Category table is not migrating. Only the product table is migrating. But with those errors. — Tanjib Rubyat, Aug 16 '21 at 15:17
@TanjibRubyat are you sure that the category migration is running before the product migration? — Aless55, Aug 16 '21 at 15:22
Only the product migration is running but not the category migration. — Tanjib Rubyat, Aug 16 '21 at 15:24
this means that the execution order is wrong... normally migration files have timestamps which declare the order in which they run — Aless55, Aug 16 '21 at 15:26
I recommend that you call `php artisan make:migration category_table` and afterwards `php artisan make:migration product_table` and copy the migration code (with the changes from both answers) into the new migration files — Aless55, Aug 16 '21 at 15:27
this simply drops all existiing tables and runs the migrations in the migration folder... — Aless55, Aug 16 '21 at 15:32
@Aless55 That worked. First created category table and then created product table. Thanks. — Tanjib Rubyat, Aug 16 '21 at 15:42

Aless55 · Answer 1 · 2021-08-16T15:10:04.607

0

$table->integer('category_id');

must be of type

$table->unsignedBigInteger('category_id');

https://laravel.com/docs/8.x/migrations#foreign-key-constraints

You will probably also have to change

$table->increments('id');

to

$table->bigIncrements('id');
//or
$table->id();

edited Aug 16 '21 at 15:10

answered Aug 16 '21 at 14:55

Aless55

2,652
2
15
26

errno: 150 "Foreign key constraint is incorrectly formed while migration

1 Answers1