Kevin Reynolds Kevin Reynolds - 4 months ago 52
SQL Question

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

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');
});
}

Answer

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.