Laravel migration will not add foreign key

Question

I am new to migrations and attempting to create 2 tables with a foreign key in one referencing an id in the other but I am getting a general failure to add key error. is there something I am missing?

error:

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

code:

    Schema::create('app_groups', function($table) {
     $table->increments('id');
     $table->string('app_name');
     $table->unsignedInteger('app_group_id');
     $table->timestamps();
  });

  Schema::create('app_to_bucket', function($table) {
     $table->increments('id');
     $table->unsignedInteger('app_group_id');
     $table->unsignedInteger('bucket_id');
     $table->timestamps();
  });
  Schema::table('app_to_bucket', function($table) {
     $table->foreign('app_group_id')->references('app_group_id')->on('app_groups')->onDelete('cascade');
  });

Answer 1

This will work for sure. Eloquent primary keys are integer with length 10 and unsigned. This is why the relation is not working.

Schema::create('app_groups', function($table) {
     $table->string('app_name');
     $table->integer('app_group_id')->length(10)->unsigned();
     $table->timestamps();
  });

  Schema::create('app_to_bucket', function($table) {
     $table->integer('app_group_id');
     $table->integer('bucket_id')->length(10)->unsigned();
     $table->timestamps();
  });
  Schema::table('app_to_bucket', function($table) {
     $table->foreign('app_group_id')->references('app_group_id')->on('app_groups')->onDelete('cascade');

Answer 2

I have solved the problem.

The issue was that Laravel automatically assumes incrementing columns as the primary key. so I needed to specify that my app_group_id was the primary key.

 Schema::create('app_groups', function($table) {
     $table->string('app_name');
     $table->integer('app_group_id');
     $table->primary('app_group_id');
     $table->timestamps();
  });

  Schema::create('app_to_bucket', function($table) {
     $table->integer('app_group_id');
     $table->integer('bucket_id');
      $table->primary('bucket_id');
     $table->timestamps();
  });
  Schema::table('app_to_bucket', function($table) {
     $table->foreign('app_group_id')->references('app_group_id')->on('app_groups')->onDelete('cascade');
  });

Answer 3

Ok, there are several problems you could meet while creating/adding foreign key constraints in MySQL-databases using Laravel.

First of all, you should check names of column's and table's you assign.

Secondly, check the database engine while creating the constraint. Refer to documentation https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html it should be InnoDB.

Schema::create('app_groups', function($table) {
     // setting up the storage engine
     $table->engine='InnoDB';
     $table->increments('id');
     $table->integer('group_id')->unsigned();
     $table->string('app_name');
     $table->timestamps();
  });

Schema::create('app_to_bucket', function($table) {
     $table->engine='InnoDB';
     $table->increments('id');
     $table->integer('app_group_id')->unsigned();
     $table->integer('bucket_id')->unsigned();
     $table->timestamps();
     $table->foreign('app_group_id')
           ->references('group_id')
           ->on('app_groups')
           ->onDelete('cascade');
})
;}

Thirdly(optional), move your assigment of constraints(foreign keys, indexes and so on) to separate migration.

Answer 4

foreign key and reference key should have same length and same type. if you set those keys satisfying that, the error will not popup :)