how to use foreign key in laravel 5.1 migration

Question

I need to use foreign key for my database but I can't do this, after run migration command in command line, I get this error :

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table samples add constraint s amples_supplier_id_foreign foreign key (supplier_id) references suppliers (id))

[PDOException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Samples migration :

Schema::create('samples', function (Blueprint $table) {
            $table->Increments('id',true);
            $table->string('variety',50);
            $table->integer('supplier_id')->unsigned();
            $table->foreign('supplier_id')->references('id')->on('suppliers');
            $table->string('lot_number');
            $table->date('date');
            $table->integer('amount');
            $table->integer('unit_id')->unsigned();
            $table->foreign('unit_id')->references('id')->on('unit');
            $table->string('technical_fact');
            $table->string('comments');
            $table->string('file_address');
            $table->integer('category_id')->unsigned();
            $table->foreign('category_id')->references('id')->on('category');
            $table->timestamps();
        });

Supplier migration :

Schema::create('suppliers', function (Blueprint $table) {
            $table->Increments('id',true);
            $table->string('supplier',50);
            $table->timestamps();
        });

I try to that with new migration for samples, but unsuccessful :

Schema::create('samples', function (Blueprint $table) {
                $table->Increments('id',true);
                $table->string('variety',50);
                $table->integer('supplier_id')->unsigned();
                $table->string('lot_number');
                $table->date('date');
                $table->integer('amount');
                $table->integer('unit_id')->unsigned();
                $table->string('technical_fact');
                $table->string('comments');
                $table->string('file_address');
                $table->integer('category_id')->unsigned();
                $table->timestamps();
        });

        Schema::table('samples', function($table) {
            $table->foreign('supplier_id')->references('id')->on('suppliers');
            $table->foreign('unit_id')->references('id')->on('unit');
            $table->foreign('category_id')->references('id')->on('category');
        });

I try to fix length of the primary key to 10, but unsuccessful again

Answer 1

Order matters.

You want to be sure that your "suppliers" table exists before attempting to reference a column on that table as a constraint.

So If you want to set your foreign key Constraint while creating your table then make sure that you create the "suppliers" migration first, and the "samples" migration afterwards:

php artisan make:migration create_suppliers_table --create=suppliers
php artisan make:migration create_samples_table --create=samples

...add schema code to your migration files. and then:

php artisan migrate

---

If you don't want to worry about the order in which the tables are created then do your create_table migrations first, without the foreign key constraints, and then do an additional migration to add your foreign keys.

php artisan make:migration create_samples_table --create=samples
php artisan make:migration create_suppliers_table --create=suppliers
php artisan make:migration alter_samples_table --table=samples   <-- add your foreign key constraints to this migration file

...add schema code to your migration files. And then migrate using:

php artisan migrate

Answer 2

At last generate a migration for the table keep in mind that they should be in order if you feel any diffuculties just name ur table_foreign_keys

 Schema::table('samples', function($table) {
            $table->foreign('supplier_id')->references('id')->on('suppliers');
            $table->foreign('unit_id')->references('id')->on('unit');
            $table->foreign('category_id')->references('id')->on('category');
        });

place all foreign keys related here at last and run

Answer 3

try like this way

Schema::table('samples', function($table) {
        $table->integer('supplier_id')->unsigned();
        $table->foreign('supplier_id')->references('id')->on('suppliers');
        $table->integer('unit_id')->unsigned();
        $table->foreign('unit_id')->references('id')->on('unit');
        $table->integer('category_id')->unsigned();
        $table->foreign('category_id')->references('id')->on('category');
    });

Answer 4

KorreyD say true ! , but I created migrations, and then renamed the migration to reorder them, It's so simple :

Before renamed :

Supplier migration : 2015_08_21_104217_supllier_table.php

Samples migration : 2015_08_22_102325_samples_table.php

After renamed :

Samples migration : 2015_08_21_102325_samples_table.php

Supplier migration : 2015_08_22_104217_supllier_table.php

my problem solved ! because supplier migration run before the samples migration

Comment : I try to this with reflector, that renamed any where that used migration name

Answer 5

Schema::table('posts', function (Blueprint $table) {
    $table->unsignedInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

Answer 6

Everyone is correct however the easiest way is to create your migrations files a usual. You will have e.g. 2019_01_21_123456_create_table_one_table.php ...

I renamed them all

2019_01_21_0010_create_table_one_table.php
2019_01_21_0020_create_table_two_table.php
2019_01_21_0030_create_table_three_table.php
2019_01_21_0040_create_table_four_table.php

Now if I need to add a migration before table_two and after table_one I can simply change it to

2019_01_21_0015_create_table_five_table.php

Now the migration order will be

2019_01_21_0010_create_table_one_table.php
2019_01_21_0015_create_table_five_table.php
2019_01_21_0020_create_table_two_table.php
2019_01_21_0030_create_table_three_table.php
2019_01_21_0040_create_table_four_table.php