Laravel migration foreign key issue

Question

I have created categories table using categories migration and then i am trying to create products table using another migration with foreign key categories_id in products table to id in products table.

Please find my migrations below.

Categories migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategoriesTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories',function(Blueprint $table)
        {
            $table->increments('id');

            $table->string('category_name', 255);
            $table->string('category_description', 255);

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('categories');
    }

}

Products migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('category_id')->unsigned();
            $table->foreign('category_id')->reference('id')->on('categories');
            $table->string('product_name');
            $table->text('product_description');
            $table->decimal('product_cost', 10, 2);
            $table->text('product_image');
            $table->boolean('product_availability')->default(1);

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('products');
    }

}

I am getting this below error when i ran php artisan migrate command in command line

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near ')' at line 1 (SQL: alter table `pr
oducts` add constraint products_category_id_foreign foreign key (`category_
id`) references `categories` ())

The Alpha · Accepted Answer · 2014-06-19T09:08:24.630

You may try this (Notice unsigned and references you have used reference):

// unsigned() should be used during declaration
$table->integer('category_id')->unsigned();

// reference() should be references()
$table->foreign('category_id')->references('id')->on('categories');

Update:

At first create the products table then add foreign key. Remove the following line when creating the table:

$table->foreign('category_id')->references('id')->on('categories');

Then add foreign key using this:

Schema::table('products', function($table) {
    $table->foreign('category_id')->references('id')->on('categories');
});

Both should be different like this:

Schema::create('products', function(Blueprint $table)
{
    $table->increments('id');
    $table->integer('category_id')->unsigned();
    $table->string('product_name');
    // more ...
});

Schema::table('products', function($table) {
    $table->foreign('category_id')->references('id')->on('categories');
});

I checked already that thread I tried those but no difference by the way i checked my db also tables get created and the foreign key was unsigned in both tables. The problem is because of this error i can't migrate future migrations — Venkata Krishna, Jun 19 '14 at 09:02
Also, if 'references()' is misspelled, it would cause the same issue :) — Mark Shehata, Dec 20 '18 at 04:15

score 1 · Answer 2 · answered Jun 19 '14 at 08:50

1

unsigned() should be defined while setting the type, not while setting the foreign key. Also, reference() should be references()

The corresponding lines should be changed like this:

$table->integer('category_id')->unsigned();
$table->foreign('category_id')->references('id')->on('categories');

answered Jun 19 '14 at 08:50

Arda

6,756
3
47
67

Still same error persists even after adding unsigned() before froeign key assigning – Venkata Krishna Jun 19 '14 at 08:56
Have you fixed the references ? – Arda Jun 19 '14 at 09:17
yeah please check above approved answers – Venkata Krishna Jun 19 '14 at 09:38

Laravel migration foreign key issue

2 Answers2

Update:

Linked

Related