Last Updated: October 27, 2018
·
89.99K
· ravinsharma7

Creating Foreign Key in Laravel Migrations

Today I was trying out Laravel's Migration.

I tried to create two tables with the second table having a foreign key that reference the first table.

The first table

<?php 
// products table
public function up()
{
       Schema::create('products', function($table){
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
}

The second table

<?php
// products pricing table
public function up()
{
    Schema::create('product_prices', function($table){

            $table->increments('id');
            $table->foreign('product_id')->references('id')->on('products');
            $table->string('price');
            $table->timestamps();
        });
}

Had this error staring into my eyes:
[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'product_id' doesn't exist in table (SQL: alter table `cs_product_prices` add constraint product_prices_product_id_foreign foreign key (`product_id`) references `cs_products` (`id`))

After groking here and there around the internet. It seems directly calling foreign method does not create the column with the reference, which my silly brain thought. So I rewrote the table into like this.


public function up()
    {
        Schema::create('product_prices', function($table){

            $table->increments('id');
            $table->integer('product_id');
            $table->foreign('product_id')->references('id')->on('products');
            $table->string('price');
            $table->timestamps();
        });
}

Now another error stared into my eyes:
[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `cs_product_prices` add constraint product_prices_product_id_foreign foreign key (`product_id`) references `cs_products` (`id`))

After a few minutes groking, I realize 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. increments() method set an unsigned integer with auto increment by default in the mysql db.

Changing the second table again:

public function up()
    {
        Schema::create('product_prices', function($table){

            $table->increments('id');
            $table->integer('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products');
            $table->string('price');
            $table->timestamps();
        });
}

Now migration works.
More info refer to following source:

  1. MySQL foreign key
  2. Laravel foreign() method

4 Responses
Add your response

Cheers

over 1 year ago ·

Hey Men your are the best!!, You help me a lot.

Thanks.

over 1 year ago ·

This was very useful. Thanks!

over 1 year ago ·

You can simply use PHP artisan command to create Laravel migration table. For that you will have to use this command:

php artisan make:migration createtodotable

over 1 year ago ·