Laravel 5.2 Migration: Cannot add foreign key of char data type

Question

I am trying to create a nullable foreign key of char data type. When I run the migrate command. I get the following error. I am not sure, where I am doing wrong.

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table levels add constraint levels_sample_type_id_foreign foreign key (sample_type_id) references sample_types (id))

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

Here is the migration file contents for levels table

public function up()
{
    Schema::create('levels', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->char('id', 36)->unique();
        $table->string('description')->nullable();
        $table->char('sample_type_id', 36)->nullable();
        $table->integer('order');
        $table->timestamps();
        $table->primary('id');
        $table->foreign('sample_type_id')->references('id')->on('sample_types');
    });
}

and for the sample_types table are as follows

public function up()
{
    Schema::create('sample_types', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->char('id', 36)->unique();
        $table->string('name');
        $table->timestamps();
        $table->primary('id');
    });
}

I am fairly positive both must be of the same type. so `sample_type_id` cannot be null. — Andrew Nolan, Jul 18 '16 at 22:28
@miken32, you are right. Table levels was created before the sample_types. I am now able to generate the foreign key without any issue. Thank you. Could you please add this comment as an answer? — Kevin Reynolds, Jul 18 '16 at 22:38

score 3 · Accepted Answer · answered Jul 18 '16 at 22:40

3

The foreign key statement will fail if the table being referred to does not exist yet. Ensure that you create the sample_types table before referring to it in the levels migration file.

answered Jul 18 '16 at 22:40

miken32

42,008
16
111
154

Laravel 5.2 Migration: Cannot add foreign key of char data type

1 Answers1

Linked