Foreign key constraint is incorrectly formed. Can you help me?

Question

Can you help me?

I use the MySQL.

I make

php artisan migrate

but error

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table learn.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))

Categories migration:

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

Products migration:

    Schema::create('products', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name', 150)->unique();
        $table->unsignedBigInteger('category_id');
        $table->string('image', 255);
        $table->boolean('sale')->default(false);
        $table->text('description');
        $table->timestamps();
        $table->softDeletes();
        $table->foreign('category_id')->references('id')->on('categories');
    });

It's because of a type mismatch between your products.category_id column and your categories.id column. You either need to make your id columns bigIncrements or make your category_id an ->integer('category_id')->unsigned() — lufc
Possible duplicate of SQLSTATE[HY000]: General error: 1005 Can't create table - Laravel 4 — Watercayman

lufc lufc · Accepted Answer · 2019-08-08T22:02:46

Replace: $table->unsignedBigInteger('category_id');

With: $table->integer('category_id')->unsigned();

OR

Replace: $table->increments('id');

With: $table->bigIncrements('id');

In both tables (but at least in your categories table).

WHY?:

From the MySQL docs: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

From the Laravel docs: https://laravel.com/docs/5.8/migrations#columns

$table->increments(): Auto-incrementing UNSIGNED INTEGER (primary key) equivalent column.

$table->unsignedBigInteger(): UNSIGNED BIGINT equivalent column.

So in your case:

$table->unsignedBigInteger('category_id'); => UNSIGNED BIGINT

$table->increments('id'); => UNSIGNED INTEGER.

They don't match.

Foreign key constraint is incorrectly formed. Can you help me?

1 Answers