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

Why aren't you adding the foreign key when the table was created? — ollieread, Feb 27 '14 at 18:57

score 18 · Answer 1 · answered May 31 '14 at 05:06

18

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

answered May 31 '14 at 05:06

Todor Todorov

2,503
1
16
15

3

Works perfect, thanks. Looking more carefylly, the documentation says: "Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned." (ref: http://laravel.com/docs/schema) – Alejandro Silva Jun 13 '14 at 21:32
Just curious, what's the default length for an integer? Is the length(10) part necessary? – Kenmore Aug 13 '14 at 07:17
The default length for the integer in MySQL is 11 but in Laravel it is 10. I do not know why. Maybe there is a reason. It got to work without lenght(10). But I am using it just for any case. – Todor Todorov Aug 17 '14 at 17:30
I googled this problem like for 5 hours and finally found your solution that saved my day thank you – Reem Aziz Oct 24 '18 at 15:44

score 7 · Accepted Answer · answered Feb 28 '14 at 20:47

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

score 3 · Answer 3 · answered Feb 21 '18 at 16:51

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.

The tables were being created as MyISAM and that was the problem. Setting InnoDB solves problem. Thank you!!! — Doctiger, Oct 11 '21 at 14:47

Antonio Carlos Ribeiro · Answer 4 · 2014-02-27T19:18:12.063

1

You must first create the table, then create the foreign keys:

Schema::create('app_to_bucket', function($table) {
    $table->increments('id');
    $table->integer('bucket_id')->unsigned();
    $table->integer('app_group_id')->unsigned();
    $table->timestamps();
});

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

edited Feb 27 '14 at 19:18

answered Feb 27 '14 at 18:47

Antonio Carlos Ribeiro

86,191
22
213
204

I have tried this and got the same error...updated the code above – arrowill12 Feb 27 '14 at 18:50
Just don't create the column, it will be added by your database server. Edited. – Antonio Carlos Ribeiro Feb 27 '14 at 18:55
I tried removing that line and it throws an error saying 'app_group_id' doesn't exist in table – arrowill12 Feb 27 '14 at 18:59
Which database is this? Try to use unsigned() as in my last edit. – Antonio Carlos Ribeiro Feb 27 '14 at 19:20
Odd, should work. Here's another accepted answer about this: http://stackoverflow.com/questions/20999161/sqlstatehy000-general-error-1005-cant-create-table-laravel-4/20999312#20999312. – Antonio Carlos Ribeiro Feb 27 '14 at 19:53
i think the issue is that the field I am referencing is not an autoincrementing field. – arrowill12 Feb 27 '14 at 20:02

score 1 · Answer 5 · answered Nov 17 '14 at 10:21

1

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

answered Nov 17 '14 at 10:21

Janaka R Rajapaksha

3,585
1
25
28

Morgan · Answer 6 · 2014-02-27T19:13:46.093

0

Try this:

Schema::create('app_groups', function($table) {
     $table->increments('id');
     $table->integer('group_id')->unsigned();
     $table->string('app_name');
     $table->timestamps();
  });
 Schema::create('app_to_bucket', function($table) {
     $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');
  });

edited Feb 27 '14 at 19:13

answered Feb 27 '14 at 18:56

Morgan

196
9

ahh i was mistaken this will not work...I need the app_group_id to be able to repeat it cannot be incrementing. can be multiple app_names with the same app_group_id – arrowill12 Feb 27 '14 at 19:05
I added the 'group_id' to 'app_groups'. I have nearly the exact same code on my app and it works fine. – Morgan Feb 27 '14 at 19:23
1

I copy pasted this exactly and it does not work. it works fine when i try to reference the incrementing id but not the group_id – arrowill12 Feb 27 '14 at 19:36
You could try wrapping your code in: DB::statement('SET FOREIGN_KEY_CHECKS = 0'); //code here DB::statement('SET FOREIGN_KEY_CHECKS = 1'); and see what happens – Morgan Feb 27 '14 at 19:44

Laravel migration will not add foreign key

6 Answers6

Linked