General error: 1215 Cannot add foreign key constraint on tables

Question

I have been trying out all possible solutions based on other stack questions and answers but I'm still not getting any success so I had to make my own question.

I have the following Schemas

Schema::create('file_data', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');

......

        Schema::create('claims', function (Blueprint $table) {
            $table->engine = 'InnoDB';
         ....
            $table->integer('file_id')->unsigned()->nullable()->default(DB::raw('NULL'));

          ....
            $table->foreign('budget_id')
                ->references('id')
                ->on('budgets')
                ->onDelete('cascade');

        });

        Schema::create('claims_details', function (Blueprint $table) {
            $table->engine = 'InnoDB';
           .........
            $table->integer('file_id')->unsigned()->nullable()->default(DB::raw('NULL'));
          ..........

        });

In another file

Schema::table('claims', function(Blueprint $table){
        $table->foreign('file_id')
            ->references('id')
            ->on('file_data')
            ->onDelete('cascade');
      });

      Schema::table('claims_details', function(Blueprint $table){
        $table->index(['invoice_date','claim_id']);
        $table->foreign('claim_id')
            ->references('id')
            ->on('claims')
            ->onDelete('cascade');
            $table->foreign('file_id')
                ->references('id')
                ->on('file_data');
      });

when I run the command php artisan migrate I get the following error

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table claims add constraint claims_file_id_ foreign foreign key (file_id) references file_data (id) on delete cascade)

The tables are Innob
The columns are of the same type
the columns are unsigned
the files run in the correct order
The only difference is that file_id needs to be null( they have to be)

Is it because the column is "null" makes it fail? I tried it without it being null and still failed. What are other causes that can cause it to have this issue?

`file_id` being nullable is fine in terms of foreign key constraints. I'm wondering why your foreign key references `file_data.id` though, and not `files.id`. Could it be that there is a 1-to-0/1 relationship between `files` and `file_data`? — e_i_pi, May 03 '17 at 23:09
If you can remove the data first before creating a foreign key i think it's work. Second option is, you must check if foreign key field value is exists with the primary key table value. — Vijunav Vastivch, May 03 '17 at 23:20
@reds Well the first option has been done as I am setting things up. Can you explain the 2nd option? — Kendall, May 08 '17 at 13:58

score 1 · Accepted Answer · answered May 08 '17 at 14:47

1

Turns out I needed to have the columns indexed

answered May 08 '17 at 14:47

Kendall

5,065
10
45
70

1

Of course, one of the idiosyncricies of InnoDB! http://stackoverflow.com/questions/6230588/foreign-keys-must-be-index-in-mysql – e_i_pi May 08 '17 at 22:51

General error: 1215 Cannot add foreign key constraint on tables

1 Answers1