@Laravel Migration: Cannot add foreign key constraint in laravel

Question

I'm trying to create foreign keys in Laravel however when I migrate my table using artisan i am thrown the following error:

λ php artisan migrate
Migration table created successfully.


[Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `fees` add constraint `fee
  s_fee_type_id_foreign` foreign key (`fee_type_id`) references `feetypes` (`fee_type_id`))


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

My migration code is as so:

fees

<?php

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

class CreateFeesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('fees', function (Blueprint $table) {
            $table->increments('fee_id');
            $table->integer('academic_id')->unsigned();
            $table->integer('level_id')->unsigned();
            $table->integer('fee_type_id');
            $table->string('fee_heading', 100)->nullable();
            $table->float('amount', 8, 2);
            $table->foreign('academic_id')->references('academic_id')->on('academics');
            $table->foreign('level_id')->references('level_id')->on('levels');
            $table->foreign('fee_type_id')->references('fee_type_id')->on('feetypes');
        });
    }

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

fesstypes

<?php

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

class CreateFeetypesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('feetypes', function (Blueprint $table) {
            $table->unsignedinteger('fee_type_id');
            $table->string('fee_type', 100);
        });
    }

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

Any ideas as to what I've done wrong, I want to get this right now, as I've got a lot of tables I need to create e.g. Users, Students, Leves, etc. Ideally I want to create tables which hold this data with the foreign keys, i..e fees and feetypes.

Hope someone can help me to get started.

I`ve posted and answer. You have to Constance on datatypes of your references — User123456, Oct 19 '18 at 05:32

score 0 · Answer 1 · answered Oct 19 '18 at 05:09

0

Replace this line:

$table->integer('fee_type_id');

With this:

$table->unsignedInteger('fee_type_id');

In CreateFeesTable Migration.

answered Oct 19 '18 at 05:09

Junaid Ahmad

599
1
4
15

try running php artisan migrate:fresh – usmanhaq Oct 19 '18 at 07:58

score 0 · Answer 2 · answered Oct 19 '18 at 05:31

Check all data types matches for the referenced data types.

public function up()
{
    Schema::create('fees', function (Blueprint $table) {
        $table->increments('fee_id');
        $table->unsignedInteger('academic_id');
        $table->unsignedInteger('level_id');
        $table->unsignedInteger('fee_type_id');
        $table->string('fee_heading', 100)->nullable();
        $table->float('amount', 8, 2);
        $table->foreign('academic_id')->references('academic_id')->on('academics');
        $table->foreign('level_id')->references('level_id')->on('levels');
        $table->foreign('fee_type_id')->references('fee_type_id')->on('feetypes');
    });
}

check level_id in levels , academic_id in academics ,fee_type_id in feetypes are also unsignedInteger or autoincrement and change your table creating script to above one.

score 0 · Answer 3 · answered Oct 19 '18 at 06:18

0

CreateFeesTable.php

change

$table->integer('fee_type_id');

to

$table->unsignedInteger('fee_type_id');

CreateFeetypesTable

change

$table->unsignedinteger('fee_type_id');

to

$table->increments('fee_type_id'); or $table->integer('fee_type_id');

answered Oct 19 '18 at 06:18

Kapitan Teemo

2,144
1
11
25

score 0 · Answer 4 · answered Oct 19 '18 at 07:38

You must first create an index on the referenced column, i.e. fee_type_id in feetypes table.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

From MySQL Reference Manual

@Laravel Migration: Cannot add foreign key constraint in laravel

4 Answers4