MySQL/Laravel Foreign key constraint is incorrectly formed

Question

I am trying to run php artisan migrate to create my mysql tables usin laravel.

I got this error: Foreign key constraint is incorrectly formed

Users Table:

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

password_resets table:

Schema::create('password_resets', function (Blueprint $table) {
            $table->string('email')->index();
            $table->string('token');
            $table->timestamp('created_at')->nullable();
        });

products table:

 Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('product_type');
            $table->integer('quantity');
            $table->timestamps();
        });

Shipments table:

  Schema::create('shipments', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('order_number')->unsigned();
        $table->integer('product_id')->unsigned();
        $table->foreign('order_number')->references('id')->on('orders');
        $table->foreign('product_id')->references('id')->on('products');
        $table->dateTime('chargecardtime');
        $table->dateTime('packingtime');
        $table->date('shiporderdate');
        $table->timestamps();
    });

Orders table:

    Schema::create('orders', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('customer_id')->unsigned();
        $table->integer('product_id')->unsigned();
        $table->foreign('customer_id')->references('id')->on('users');
        $table->foreign('product_id')->references('id')->on('products');
        $table->string('name');
        $table->string('to_street');
        $table->string('to_city');
        $table->date('ship_date');  
        $table->string('phone');
        $table->timestamps();
    });

Exception trace:

1 PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table ec.#sql-3664_86 (errno: 150 "Foreign key constraint is incorrectly formed")")

i guess there is a problem with orders table since after error i cant see that table in database.others are created.

i guess there is a problem with orders table since after error i cant see that table in database.others are created. — dongerpep, Mar 19 '18 at 21:12

score 2 · Accepted Answer · answered Mar 19 '18 at 20:15

2

Since you're referencing an id you need to make the foreign key column unsigned. As ids are by default unsigned (non-negative).

So do this for all your foreign keys:

$table->integer('product_id')->unsigned();

answered Mar 19 '18 at 20:15

DevK

9,597
2
26
48

Make sure that you really dropped all previously created fields and ran the migration on a fresh database. – DevK Mar 19 '18 at 21:06
Check in the database which table is the last created and which one should be next in your migration. That way you can easily tell which one fails. Make sure they're in order you posted them in – DevK Mar 19 '18 at 21:35

score 0 · Answer 2 · answered Mar 19 '18 at 20:20

you should add unsigned() method to your foreign keys columns, like $table->integer('product_id')->unsigned() cause they must be exactly the same column types as the key that they are referenced. the referenced keys are probably unsigned integers, that's why you got the error. in MySQL, integer signed and integer unsigned are different types

score 0 · Answer 3 · answered Sep 10 '19 at 11:32

0

$table->integer('product_id')->unsigned();

try this

$table->unsignedBigInteger(');

answered Sep 10 '19 at 11:32

avishkaME

114
1
2
8

MySQL/Laravel Foreign key constraint is incorrectly formed

3 Answers3

Linked